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

[santoshrai]

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)

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) ;

[sunny_talwar · Accepted solution]

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

[santoshrai]

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.....