Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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 |
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;
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;
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.
awesome axample - thanks!
ahhh - first sorted value - thanks.
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?