Adding a flag to the earliest action for each case

[#1]

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

[gmarshall]

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

[MarcoWedel]

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