Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi
Wondering if you can help, i have some data in the format of the table below. I need to add a flag (flag must be a 1 not a date) to the earliest closure record of each case
Can anyone think of the best approach? I am adding the flag through a resident load which is joined back to the original table. I'm not sure whether i need to write a loop to look at all records under each case id, if so i'd appreciate some guidence on that. Thank you in advance
Case ID | Status | Status Date |
---|---|---|
001 | Closed | 01/02/2014 |
001 | Closed | 01/03/2014 |
001 | Closed | 03/03/2014 |
I recon you are on the right track Ashley.
Worked example should clarify / confirm - see attached data & QVW. Note that my data will also show that should there be multipe records for a single case with the same Close Date then both will be flagged.
Let me know how you get on.
Geoff
Hi,
one solution:
table1:
LOAD *
FROM [http://community.qlik.com/thread/130144]
(html, codepage is 1252, embedded labels, table is @1);
Left Join (table1)
LOAD *, 1 as flag;
LOAD [Case ID], Status, Min([Status Date]) as [Status Date]
Resident table1
Where Status = 'Closed'
Group By [Case ID], Status;
hope this helps
regards
Marco