Qlik Community

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

Rerturning only the max date

Hey All,
I'm having trouble here.  I have data that look like this:
AIRCAFT_IDSTATUS_TYPE_IDEVENT_DATE
31U6/13/2012
31D2/14/2013
31N2/2/2012
32U1/12/2010
32D2/2/2011
33D5/6/2012
33U12/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_IDSTATUS_TYPE_IDEVENT_DATE
31U2/14/2013
32D2/2/2011
33D5/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

1 Solution

Accepted Solutions
Not applicable
Author

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_IDSTATUS_TYPE_IDEVENT_DATE
31D2/14/2013
32D2/2/2011
33D5/6/2012

View solution in original post

3 Replies
sujeetsingh
Master III
Master III

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().

Not applicable
Author

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

Not applicable
Author

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_IDSTATUS_TYPE_IDEVENT_DATE
31D2/14/2013
32D2/2/2011
33D5/6/2012

View solution in original post