Qlik Community

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
QlikWorld 2022, LIVE in Denver CO., May 16-19, 2022. REGISTER NOW TO RECEIVE EARLY BIRD PRICING
cancel
Showing results for 
Search instead for 
Did you mean: 
haymarketpaul
Creator III
Creator III

Find highest date from many date fields

I have some data with many date columns.  I need to find the highest date per record by comparing all the date columns against each other.  Either in a straight table chart column or somehow within the LOAD script.

data eg...

EmailSource1Source2Source3Source4Expected Result
1@email.com
12/07/2014
02/02/2014
23/10/2014
09/04/2014
Here it should flag source3
2@email.com
01/03/2014
04/01/2014
01/03/2014
10/01/2014
Here 2 highest dates are the same so should flag source1 & source3
3@email.com
03/05/2014
07/11/2014
12/02/2014
02/06/2014
Here it should flag source2
4@email.com
02/04/2014
14/05/2014
07/08/2014
12/12/2014
Here it should flag source4

Any ideas about how best to do this date comparison much appreciated

1 Solution

Accepted Solutions
MK_QSL
MVP
MVP

Data:

Load *,

  IF(RangeMax(Source1,Source2,Source3,Source4)=Source1,'Source1') as MaxSource1,

  IF(RangeMax(Source1,Source2,Source3,Source4)=Source2,'Source2') as MaxSource2,

  IF(RangeMax(Source1,Source2,Source3,Source4)=Source3,'Source3') as MaxSource3,

  IF(RangeMax(Source1,Source2,Source3,Source4)=Source4,'Source4') as MaxSource4

Inline

[

  Email, Source1, Source2, Source3, Source4,

  1@email.com,12/07/2014,02/02/2014,23/10/2014,09/04/2014

  2@email.com,01/03/2014,04/01/2014,01/03/2014,10/01/2014

  3@email.com,03/05/2014,07/11/2014,12/02/2014,02/06/2014

  4@email.com,02/04/2014,14/05/2014,07/08/2014,12/12/2014

];

Left Join (Data)

Load

  Email,

  Trim(MaxSource1&' '&MaxSource2&' '&MaxSource3&' '&MaxSource4) as MaxSource

Resident Data;

Drop Fields MaxSource1,MaxSource2,MaxSource3,MaxSource4;

View solution in original post

3 Replies
CELAMBARASAN
Partner
Partner

Use

=Date(RangeMax(Source1, Source2, Source3, Source4))

which will give you the Highest date value

Source1, Source2, Source3, Source4 should be date format not as text.

MK_QSL
MVP
MVP

Data:

Load *,

  IF(RangeMax(Source1,Source2,Source3,Source4)=Source1,'Source1') as MaxSource1,

  IF(RangeMax(Source1,Source2,Source3,Source4)=Source2,'Source2') as MaxSource2,

  IF(RangeMax(Source1,Source2,Source3,Source4)=Source3,'Source3') as MaxSource3,

  IF(RangeMax(Source1,Source2,Source3,Source4)=Source4,'Source4') as MaxSource4

Inline

[

  Email, Source1, Source2, Source3, Source4,

  1@email.com,12/07/2014,02/02/2014,23/10/2014,09/04/2014

  2@email.com,01/03/2014,04/01/2014,01/03/2014,10/01/2014

  3@email.com,03/05/2014,07/11/2014,12/02/2014,02/06/2014

  4@email.com,02/04/2014,14/05/2014,07/08/2014,12/12/2014

];

Left Join (Data)

Load

  Email,

  Trim(MaxSource1&' '&MaxSource2&' '&MaxSource3&' '&MaxSource4) as MaxSource

Resident Data;

Drop Fields MaxSource1,MaxSource2,MaxSource3,MaxSource4;

View solution in original post

haymarketpaul
Creator III
Creator III
Author

Brilliant thank you - works perfectly