Sort by the timestamp and select only the last status for each ID

[Amelia_96]

Hi, i'm trying to sort my data according to the timestamp in qlikview script. Then i want to select only the last status for each ID. How can i write this in the script?

Here's my sample data:

And the expected output:

Thank you in advance!!

[QFabian · Accepted solution]

Hi, try this :

X:

Load 

   ID,

    max(Timestamp)  as MaxTimeStamp

From yor source

   group by ID;

left join

Load

ID,

Timestamp as MaxTimeStamp,

Status as LastStatus

Resident yuour source;

[QFabian]

This is how i transformed the text field to a valid timestamp :

MonthX:
mapping
LOAD * INLINE [
F1, F2
JAN, 1
FEB, 2
MAR, 3
APR, 4
MAY, 5
JUN, 6
JUL, 7
AUG, 8
SEP, 9
OCT, 10
NOV, 11
DEC, 12
];

TIMESTAMP:
LOAD * INLINE [
TIMESTAMP
03FEB2020 :16:42:36
];

Load
makedate(right(subfield(TIMESTAMP, ' ' ,1), 4) , applymap('MonthX',mid(subfield(TIMESTAMP, ' ' ,1), 3,3)), left(subfield(TIMESTAMP, ' ' ,1), 2)) + mid(subfield(TIMESTAMP, ' ' ,2),2) as TIMESTAMP2,
mid(subfield(TIMESTAMP, ' ' ,2),2) as hora
Resident TIMESTAMP;

[tresesco · Accepted solution]

@Amelia_96    Try like:

Load

     ID,

     Timestamp(Max(Timestamp)) as Timestamp,

     FirstSortedValue(Status, -Timestamp) as Status

From <> Group by ID;

[edwin · Accepted solution]

another option, create a chart with only calculated dimensions (replace Date with timestamp):

[edwin]

forgot to mention in your dimensions, set SUPPRESS WHEN VALUE IS NULL

[Amelia_96]

Thank you @edwin 

[Amelia_96]

Thank you @QFabian 

[Amelia_96]

Thank you @tresesco