max date value

[tinkerz1]

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];

[swuehl · Accepted solution]

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

[#3]

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;