8 Replies Latest reply: May 15, 2014 10:04 AM by Grant Perkins RSS

    Date extraction

    Bradl Vogl

      I have a list of many part numbers that have been received hundreds of times over the last 10 years.

      I would like to extract the (3) most recent receiving dates for each part number.

       

      Fields are:

       

      Part number

      Qty received

      Date received

       

      Any thoughts????

       

      I appreciate any help I can get.

       

      Regards,

      Brad Vogl

        • Date extraction
          Data Kruncher

          Hi Brad,

           

          I think that it might be time for you to move to v10, if you haven't yet. Your profile indicates v9.

           

          I whipped up this little Excel table:

          Part    Qty    Date

          100    50    1/1/2009

          100    75    2/1/2009

          100    100    3/1/2009

          100    125    4/1/2009

          100    150    5/1/2009

          200    50    1/1/2009

          200    75    2/1/2009

          200    100    3/1/2009

          200    125    4/1/2009

          200    150    5/1/2009

          300    50    1/1/2009

          300    75    2/1/2009

          300    100    3/1/2009

          300    125    4/1/2009

          300    150    5/1/2009[/code]Excuse the poor alignment here; they're proper columns in Excel.

           

          I opened the Excel file as a database, then created a summary in v10 as follows:

           

          Part number as the first key

          Date (descending) as the second key

          Qty as the only measure

          /LIST

          I then went to the Matching tab for the Date, and set the First "3" values, and (here's the big v10 benefit) selected the option to "Hide unmatched value rows". That prevents the "All Others" message and value(s) that you get in v9.

           

          The end result is this:

          Part   Date          Qty

          100    2009-05-01    150

          100    2009-04-01    125

          100    2009-03-01    100

          200    2009-05-01    150

          200    2009-04-01    125

          200    2009-03-01    100

          300    2009-05-01    150

          300    2009-04-01    125

          300    2009-03-01    100

          /codeOddly, the date format changed when I pasted from V10 to this post, but you get the idea.

           

          You might be able to accomplish this other ways (macro programming?), but I suspect that it'll be much more effort, and that alone could justify the upgrade. It takes only a minute or so to build the summary that I think does exactly what you want.

           

          HTH,

          Kruncher

            • Date extraction
              Data Kruncher

              OK, another idea already if you don't have v10.

               

              Prepare the same summary in v9. That'll get you the include "All Others" rows.

               

              Export the summary to your format of choice, and then create another model with that export as the data source.

               

              The Date field will need to be Character. Filter out all of the "All Other" records, build a new calculated field using a CtoD function to get proper dates, and you're almost done.

               

              Quickly create a small batch file to handle the two pass system.

               

              Now you're done.

               

              So much for the macro programming... :rolleyes:

            • Date extraction
              Bradl Vogl

              Kruncher,

                   It seems to be returning only records that were recieved in the last (3) actual calendar days as opposed to the actual last (3) receiving days.

                   All the part numbers displayed in the summary have been recieved in the last 3 calendar days.

                   Below is a part number example from my database. Part # 931236-100

               

              931236-100 2008-07-16 2000

              931236-100 2008-07-29 2000

              931236-100 2008-08-19 2000

              931236-100 2008-09-04 2000

              931236-100 2008-09-24 1000

              931236-100 2008-10-15 2000

              931236-100 2008-10-20 2000

              931236-100 2008-12-04 3000

              931236-100 2009-01-05 1500

              931236-100 2009-01-14 1000

              931236-100 2009-01-30 1500

              931236-100 2009-02-11 5000

              931236-100 2009-02-12 1

              931236-100 2009-03-23 2200

              931236-100 2009-04-20 500

              931236-100 2009-05-06 3000

              931236-100 2009-05-27 3000

               

              The summary should return:

               

              931236-100 2009-04-20 500

              931236-100 2009-05-06 3000

              931236-100 2009-05-27 3000

               

              It only returns

               

              931236-100 2009-05-27 3000

               

              Thoughts?

               

              Regards,

              Brad Vogl