Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
zagzebski
Creator
Creator

Get min date over a group of fields

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:

IDUserTimestamp
1ZAG01/01/2014
2KUH04/01/2014
1OWE03/01/2014
2LUE02/01/2014
3LAN05/01/2014

The result would be:

IDUserTimestamp (min)
1ZAG01/01/2014
2LUE02/01/2014
3LAN05/01/2014
1 Solution

Accepted Solutions
MK_QSL
MVP
MVP

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;

View solution in original post

5 Replies
MK_QSL
MVP
MVP

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
Employee
Employee

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
Creator
Creator
Author

awesome axample - thanks!

zagzebski
Creator
Creator
Author

ahhh - first sorted value - thanks.

zagzebski
Creator
Creator
Author

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?