Max of two date fields

[andrew001]

Hi,

I have this table in input (tbl_INPUT), and I need to find, for each ID, the max DATE and, within the same date, the max DATE_INSERT (system timestamp).

thanks

Andrew

tbl_INPUT:

LOAD *
inline
[
ID, DATE, DATE_INSERT
1230,31-dic-09,14/08/2008 13:19
1230,31-dic-09,01/08/2010 16:13
1235,14-ago-08,10/08/2008 17:19
1235,14-ago-08,10/08/2008 16:11
1236,01-gen-09,02/02/2009 12:18
];


the desired output:
 
1230,31-dic-09,14/08/2008 13:19
1235,14-ago-08,10/08/2008 17:19
1236,01-gen-09,02/02/2009 12:18

[maxgro · Accepted solution]

this is to get one row for every ID, the first order by DATE desc and DATE_INSERT desc

tbl_INPUT:
LOAD * 
inline
[
ID, DATE, DATE_INSERT
1230,31-dic-09,14/08/2008 13:19
1230,31-dic-09,01/08/2010 16:13
1235,14-ago-08,10/08/2008 17:19
1235,14-ago-08,10/08/2008 16:11
1236,01-gen-09,02/02/2009 12:18
];


tbl_OUTPUT:
NoConcatenate
load 
ID, DATE, DATE_INSERT
Resident tbl_INPUT
where ID <> peek(ID)
order by ID, DATE desc, DATE_INSERT desc;

DROP Table tbl_INPUT;

[MK_QSL]

Use below script

tbl_INPUT:

LOAD

  ID,

  Date(Date#(DATE, 'DD-MMM-YY')) as DATE,

  Timestamp#(DATE_INSERT,'DD/MM/YYYY hh:mm') as DATE_INSERT

inline

[

ID, DATE, DATE_INSERT

1230,31-dec-09,14/08/2008 13:19

1230,31-dec-09,01/08/2010 16:13

1235,14-aug-08,10/08/2008 17:19

1235,14-aug-08,10/08/2008 16:11

1236,01-jan-09,02/02/2009 12:18

];

Now create a Straight Table

Dimension

ID

Expression

=FirstSortedValue(DATE_INSERT, -Aggr(Date(FirstSortedValue(DATE, -Aggr(Max(DATE),ID))),DATE))

[MK_QSL]

or use below

Dimension

ID

Expression

FirstSortedValue(DISTINCT Aggr(Max(DATE_INSERT),ID,DATE),-DATE)