Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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 |
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
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 |
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().
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
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 |