topic Re: how get max date records in script...??? in QlikView

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

santoshrai — Fri, 18 Dec 2015 05:55:26 GMT

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 — Fri, 18 Dec 2015 11:31:12 GMT

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 — Sun, 20 Dec 2015 05:00:52 GMT

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