Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

harjit_nar
New Contributor

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)

Tags (1)
2 Replies
JustinDallas
Valued Contributor

Re: Select Max Date for unique field value

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

;

Re: Select Max Date for unique field value

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;

Community Browser