Find highest date from many date fields

[haymarketpaul]

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

Email	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

[CELAMBARASAN]

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 · Accepted solution]

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;

[haymarketpaul]

Brilliant thank you - works perfectly