Rerturning only the max date

[#1]

Hey All,

I'm having trouble here.  I have data that look like this:

AIRCAFT_ID	STATUS_TYPE_ID	EVENT_DATE
31	U	6/13/2012
31	D	2/14/2013
31	N	2/2/2012
32	U	1/12/2010
32	D	2/2/2011
33	D	5/6/2012
33	U	12/3/2011

What I want is, using a load script, to load in the value of STATUE_TYPE_ID for each AIRCRAFT_ID based on the MAX of EVENT_DATE.  So for example, for the given data, this is what I want loaded into my dashboard.

AIRCAFT_ID	STATUS_TYPE_ID	EVENT_DATE
31	U	2/14/2013
32	D	2/2/2011
33	D	5/6/2012

So you can see that only one STATUS_TYPE_ID is loaded in per AIRCRAFT_ID based on the maximum event_date.  I tried using max(event_date, 'dd/MM/yyyy'), date(max(date(event_date,'dd/MM/yyyy')) but had no luck.  Any help would be greatly appreciated.  Thanks.

-Chris

[sujeetsingh]

Hi if you are using sql for data then easily use the max() function with group by on ID.

Else take ID as dimension and then use condition in set analysis or with if().

[#3]

Hi ,

Try this in your script.

Sample1: //this is your data

LOAD

    AIRCAFT_ID,

    STATUS_TYPE_ID,

    EVENT_DATE

from .....

Sample2: //to get max date per id

LOAD

AIRCAFT_ID,

max(EVENT_DATE) as EVENT_DATE

Resident Sample1

group by AIRCAFT_ID;

left join(Sample2) //to attach status per id

LOAD *

Resident Sample1;

DROP Table Sample1; //droping other data you dont need

-------------------------------

OR simply,

-------------------------------

Sample1: //this is your data

LOAD

    AIRCAFT_ID,

    STATUS_TYPE_ID,

    EVENT_DATE

from .....

right join(Sample1)

LOAD

AIRCAFT_ID,

max(EVENT_DATE) as EVENT_DATE

Resident Sample1

group by AIRCAFT_ID;

Regards,

Alex

[#4 · Accepted solution]

HI

TRY THIS

LOAD AIRCAFT_ID ,

    MAX(DATE(EVENT_DATE)) AS EVENT_DATE,

    FirstSortedValue(STATUS_TYPE_ID,-EVENT_DATE) AS STATUS_TYPE_ID

    Group BY AIRCAFT_ID;

LOAD AIRCAFT_ID,

     STATUS_TYPE_ID,

     EVENT_DATE

FROM

(ooxml, embedded labels, table is Sheet1);

THEN OUTPUT LIKE THIS

AIRCAFT_ID	STATUS_TYPE_ID	EVENT_DATE
31	D	2/14/2013
32	D	2/2/2011
33	D	5/6/2012