Get min date over a group of fields

[zagzebski]

I haven't be been able to find a discussion to exactly match my issue.

I want to retrieve ONLY the record with a minimum date over a the group fields (ID, User) from a table that I have loaded. The sample is:

ID	User	Timestamp
1	ZAG	01/01/2014
2	KUH	04/01/2014
1	OWE	03/01/2014
2	LUE	02/01/2014
3	LAN	05/01/2014

The result would be:

ID	User	Timestamp (min)
1	ZAG	01/01/2014
2	LUE	02/01/2014
3	LAN	05/01/2014

[MK_QSL · Accepted solution]

Temp:

LOAD ID,

     User,

     Timestamp

FROM

[http://community.qlik.com/thread/127412]

(html, codepage is 1252, embedded labels, table is @1);

NoConcatenate

Final:

Load

  ID,

  FirstSortedValue(User,Timestamp) as User,

  FirstSortedValue(Timestamp,Timestamp) as MinT

Resident Temp

Group By ID;

Drop Table Temp;

[JonnyPoole]

With ID and User ad dimensions, I added the following expression to represent the min timestamp. Without definining an else clause, the non min values would be missing and with the presentation tab -> suppress missing checkbox enabled you should lose the unwanted (non-min values).

if(

only(Timestamp)=

min( total  <ID> Timestamp ), only(Timestamp))

ps: i noticed your example showed min values over ID and not the combination of ID and User. if you need a more complicated example just let me know.

[zagzebski]

awesome axample - thanks!

[zagzebski]

ahhh - first sorted value - thanks.

[zagzebski]

quick followup - so if my real example has 15 fields in the table I would have to this type of statement for all 15 fields:

FirstSortedValue(User,Timestamp) as User,

FirstSortedValue(Dept,Timestamp) as Dept,

FirstSortedValue(Location,Timestamp) as Location,

and so on.....

...only swapping out the "User" field for the other 15 fields?