Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

zagzebski
Contributor

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
Tags (2)
1 Solution

Accepted Solutions

Re: Get min date over a group of fields

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;

5 Replies

Re: Get min date over a group of fields

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;

Employee
Employee

Re: Get min date over a group of fields

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
Contributor

Re: Get min date over a group of fields

awesome axample - thanks!

zagzebski
Contributor

Re: Get min date over a group of fields

ahhh - first sorted value - thanks.

zagzebski
Contributor

Re: Get min date over a group of fields

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?

Community Browser