topic Re: sort & firstvalue in QlikView

sort & firstvalue

Tue, 25 Nov 2014 17:31:17 GMT

Customer #	Start Date	End date	Contract	To be picked in results final
12	Nov-5-2013	Nov-6-2014	XYZ	dropped
12	Nov-5-2014	Nov-8-2015	PTR	yes because End date is later for same customer
123	Oct-15-2014	Dec-16-2015	YYZ	yes only record for that customer
324	Nov-5-2015	Nov-6-2016	TYP	dropped
345	Nov-5-2016	Nov-6-2016	XPX	yes only record for that customer
324	Nov-7-2015	Nov-6-2016	DDY	yes because start date is later for same customer
Final results
12	Nov-5-2014	Nov-8-2015	PTR
123	Oct-15-2014	Dec-16-2015	YYZ
345	Nov-5-2016	Nov-6-2016	XPX
324	Nov-7-2015	Nov-6-2016	DDY

How can i achieve above results. I have bunch of customers where latest contract needs to be picked. The criteria for latest contract is:

1) Where end date is latest, even if start date varies.

2) If end date is same however start date varies then pick the latest start date.

Re: sort & firstvalue

maxgro — Tue, 25 Nov 2014 17:55:24 GMT

in a script

with a resident load order by end date desc and also start date desc

use the peek function to only get first customer

Re: sort & firstvalue

MarcoWedel — Tue, 25 Nov 2014 21:50:34 GMT

Hi,

one solution could be:

table1:
LOAD [Customer #],
    Date#([Start Date],'MMM-DD-YYYY') as [Start Date],
    Date#([End date],'MMM-DD-YYYY') as [End Date],
    Contract
FROM [http://community.qlik.com/thread/143528]
(html, codepage is 1252, embedded labels, table is @1, filters(Remove(Row, Pos(Top, 12)),Remove(Row, Pos(Top, 11)),Remove(Row, Pos(Top, 10)),Remove(Row, Pos(Top, 9)),Remove(Row, Pos(Top, 8)),Remove(Col, Pos(Top, 5))));

Right Join
LOAD [Customer #],
    Max([Start Date]) as [Start Date]
Resident table1
Group By [Customer #];

hope this helps

regards

Marco

Re: sort & firstvalue

Tue, 25 Nov 2014 23:33:08 GMT

Imagine you have the following table:

FieldA, FieldB

A, 3

B, 4

C, 1

D, 2

If you run a FirstSortedValue(FieldA, FieldB) you'll get the first sorted value from FieldA based on the sorting of FieldB. Value 1 is the first value for FieldB so the function will in this example return C.