topic Re: Find highest date from many date fields in QlikView

Find highest date from many date fields

haymarketpaul — Wed, 25 Feb 2015 11:06:26 GMT

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...

Source1

Source2

Source3

Source4

Expected 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

Re: Find highest date from many date fields

CELAMBARASAN — Wed, 25 Feb 2015 11:13:26 GMT

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.

Re: Find highest date from many date fields

MK_QSL — Wed, 25 Feb 2015 11:24:42 GMT

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;

Re: Find highest date from many date fields

haymarketpaul — Wed, 25 Feb 2015 12:07:44 GMT

Brilliant thank you - works perfectly