Do not input private or sensitive data. View Qlik Privacy & Cookie Policy.
Skip to main content

Announcements
Qlik Open Lakehouse is Now Generally Available! Discover the key highlights and partner resources here.
cancel
Showing results for 
Search instead for 
Did you mean: 
Anonymous
Not applicable

Select Max Date for unique field value

Hi,

I have a table that looks similar to this:

User IDDate
101/10/16
102/10/16
104/10/16
107/10/16
201/10/16
203/10/16
204/10/16
301/10/16

I would like to select both the MIN/MAX date for each user ID at load. Does anyone know how to do this? (within the script)

2 Replies
JustinDallas
Specialist III
Specialist III

Try this.

LOAD [User ID], Max(Date), Min(Date)

GROUP BY [User ID]

A demo script of the statement in action is below

Demo:

DatesTable:

LOAD [User ID], Max(Date), Min(Date)

GROUP BY [User ID]

;

LOAD * Inline

[

  'User ID' , Date

  1 , 01/10/16

  1 , 02/10/16

  1 , 04/10/16

  1 , 07/10/16

  2 , 01/10/16

  2 , 03/10/16

  2 , 04/10/16

  3 , 01/10/16

]

;

EXIT Script

;

sunny_talwar

May be this

Table:

LOAD [User ID],

     Date,

     OtherFields

FROM ....

TempTable:

NoConcatenate

LOAD  [User ID],

     Min(Date) as Date

Resident Table

Group By [User ID];

Concatenate (TempTable)

LOAD [User ID],

     Max(Date) as Date

Resident Table

Group By [User ID];


Right Join (Table)

LOAD *

Resident TempTable;


DROP Table TempTable;