2 Replies Latest reply: Dec 20, 2015 12:00 AM by Santosh Roy RSS

    how get max date records in script...???

    Santosh Roy

      hi,

       

      issue : I have tried to get record in script from table based on max date but unable to get it.

      background : I have price amendment table, which contents item wise date wise price change history for all the items.

      example : item xyz may have amend 10 times, so table will have date wise 10 records along with some other information.


      i want : i wanted to pick the max date  or latest record for the item along with other details


      what i tried : i have tried group by but unable to get desired output

       

      my data base :

      i wanted to have record which is highlighted in yellow colour  (i.e  wanted to have record which has max amendment date along with other details)

      (constraint : no aggregation for rate column)

      2015-12-18_11-04-41.jpg

      i used following code but no desired out put

       

      load*

      distinct (item & vendor & plant) as item_vendor_plant_key,

      max(amendment),

      max(date (amendment date)),

      rate          //----i donot want to use aggregation here nor i wanted to put it in group by---

      resident poam_table

      group by (item & vendor & plant) ;

        • Re: how get max date records in script...???
          Sunny Talwar

          Try this:

           

          LOAD Distinct (item & vendor & plant) as item_vendor_plant_key,

                    FirstSortedValue(amendment, -[amendment date]) as MaxDateAmendment,

                    Date(Max([amendment date])) as MaxAmendmentDate,

                    FirstSortedValue(rate, -[amendment date]) as MaxDateRate

          Resident poam_table

          Group By item, vendor, plant;


          UPDATE: Missed the negative sign next to amendment date in the first sorted value

            • Re: how get max date records in script...???
              Santosh Roy

              Excellent solution,

               

              it worked and produced desirable output for me. Many many thanks buddy,

               

              Mean while i have  found alternate solution for the same (it is quite lengthy but works good too)

               

              step 1st : Create key in poam_table (item_vendor_plant_amd_key)

               

              step 2nd:

              left join (poam_table)

              Load

              Distinct item & vendor & plant & max(amendment) as item_vendor_plant_amd_key,

              'max_amendment_no' as max_amdendment_flag

              resident poam_table;



              step 3rd:

              poam_new:

              load*,

              .

              .


              resident poam_table

              where max_amdendment_flag='max_amendment_no';

              drop table poam_table;



              but once again many thanks to you...you saved my time.....