Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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;
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;
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))
or use below
Dimension
ID
Expression
FirstSortedValue(DISTINCT Aggr(Max(DATE_INSERT),ID,DATE),-DATE)