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: 
adimiz123
Contributor III
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 

 

Labels (3)
3 Replies
tresesco
MVP
MVP

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

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

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;