Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Really stuck
How do I get the max date from this list?
ID Download Month-Year Item
1 1/11/2015
2 1/09/2015
3 1/11/2015
4 1/10/2015
[CT2]:
load
max([Download Month-Year Item2],1) as [Download Month-Year Item2]
group by [Download Month-Year Item2];
load
max([Download Month-Year Item],1) as [Download Month-Year Item2]
RESIDENT [OrDownload]
group by
[ID];
SET DateFormat = 'D/MM/YYYY';
LOAD
Max([Download Month-Year Item]) as MaxDate
INLINE [
ID, Download Month-Year Item
1 , 1/11/2015
2 , 1/09/2015
3 , 1/11/2015
4 , 1/10/2015
];
edit: Or formatted as Date:
LOAD
Date(Max([Download Month-Year Item])) as MaxDate
SET DateFormat = 'D/MM/YYYY';
LOAD
Max([Download Month-Year Item]) as MaxDate
INLINE [
ID, Download Month-Year Item
1 , 1/11/2015
2 , 1/09/2015
3 , 1/11/2015
4 , 1/10/2015
];
edit: Or formatted as Date:
LOAD
Date(Max([Download Month-Year Item])) as MaxDate
hi,
if you want maxdate in the table then use this,
tab:
load * INLINE [
ID, Download Month-Year Item
1, 1/11/2015
2, 1/09/2015
3, 1/11/2015
4, 1/10/2015
] ;
load max(date([Download Month-Year Item])) as MAX_DATE Resident tab;
drop Table tab;
-----------------------------------------------------------------------------------------------------------------------
and if you want maxdate on the basis of ID, Then use this
tab:
load * INLINE [
ID, Download Month-Year Item
1, 1/11/2015
2, 1/09/2015
3, 1/11/2015
4, 1/10/2015
] ;
load ID,
FirstValue([Download Month-Year Item]) as maxdate
Resident tab
group by ID Order By [Download Month-Year Item] desc;
drop Table tab;
*********************************************
you can also use this
load * INLINE [
ID, Download Month-Year Item
1, 1/11/2015
2, 1/09/2015
3, 1/11/2015
4, 1/10/2015
] ;
load ID,
max(date([Download Month-Year Item])) as maxdate
Resident tab
group by ID ;
drop Table tab;