Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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.
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
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
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.