Qlik Community

Qlik Sense App Development

Discussion board where members can learn more about Qlik Sense App Development and Usage.

Highlighted
adimiz123
New Contributor III

How to pick the maximum and minimum date for each row ?

Hello everyone,
I need help to find the maximum and minimum date for each row or DealID (in this case).

My source table looks like this

11111111111.JPG

I'm trying to make it...

2222222222222222.JPG

Thank you very much, Adiel 

 

3 Replies
MVP
MVP

Re: How to pick the maximum and minimum date for each row ?

You could try with range functions, i.e. - RangeMin() and RangeMax()
shiveshsingh
Honored Contributor

Re: How to pick the maximum and minimum date for each row ?

Hi

 

T:LOAD * INLINE [
DealID, Date1, Date2, Date3
1010, 10/02/2018, 20/03/2018, 04/01/2018
1234, 28/08/2018, 14/01/2018, 22/07/2018
];


D:CrossTable(Date1,Date) load *Resident T;

drop table T;
NoConcatenate
F:
load Date(Date#(Date,'DD/MM/YYY'),'DD/MM/YYY')as Date, Date1,DealID
Resident D;

drop Table D;

 

 

Dimension - DealID

Exp - date(max(Date)), date(min(Date))

vitaliichupryna
Contributor II

Re: How to pick the maximum and minimum date for each row ?

Hi, 

You can do this in following way:

_Data:
Load * Inline [
DeadID, Date1, Date2, Date3
1010, 10/02/2018, 20/03/2018, 04/01/2018,
1234, 28/08/2018, 14/01/2018, 22/07/2018
];

Data:
CrossTable(Date1, Data)
LOAD DeadID,
Date1,
Date2,
Date3
Resident _Data;

DROP Table _Data;

MinMax:
LOAD
DeadID,
Date(Max(Data)) AS MaxDate,
Date(Min(Data)) AS MinDate
Resident Data
Group By DeadID;

DROP Table Data;