Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

Latest values based on time stamp

TABLE1: 

TIME_STAMP                            EMP_ID         CAL_TYPE

  1/6/2016 12:40:37 PM            123                     L1

  1/5/2016 1:21:06 PM              123                     L2

  11/5/2015 1:17:40 PM             456                    M1

  12/1/2015 12:48:40 PM            456                    M2

  12/8/2015 4:37:50 AM              789                   J1

  10/26/2015 6:13:56 PM             276                   L1

  10/26/2015 6:47:56 PM             276                    L2

  12/28/2015 6:26:59 AM             999                    M1

TABLE2:

EMP_ID       EMP_NAME

123              AAA

456              BBB

789              CCC

276              DDD

999              EEE

my code is

NEW1:

LOAD

EMP_ID,

EMP_NAME

FROM TABLE2

NEW2:

LOAD

TIME_STAMP,

EMP_ID,

CAL_TYPE

LEFT JOIN (NEW2)
LOAD *
Resident NEW1;
DROP TABLE
NEW1;

My requirement is i want the latest CAL_TYPE of a EMP_NAME

the out put is

EMP_NAME    CAL_TYPE

AAA               L1

BBB               M1

CCC               J1   ............................

Can i get the latest CAL_TYPE for A EMP_NAME in the script itself rather than going for expression in the staright table(i have some other expressions creating some problem to me in the staright table)

1 Reply
sunny_talwar

Try this:

Table1:

LOAD * Inline [

EMP_ID,      EMP_NAME

123,              AAA

456,              BBB

789,              CCC

276,              DDD

999,              EEE

];

Join(Table1)

LOAD Timestamp(Max(TIME_STAMP)) as TIME_STAMP,

  EMP_ID,

  FirstSortedValue(CAL_TYPE, -TIME_STAMP) as CAL_TYPE

Group By EMP_ID;

LOAD * Inline [

TIME_STAMP,                            EMP_ID,        CAL_TYPE

  1/6/2016 12:40:37 PM,            123,                    L1

  1/5/2016 1:21:06 PM,              123,                    L2

  11/5/2015 1:17:40 PM,            456,                    M1

  12/1/2015 12:48:40 PM,            456,                    M2

  12/8/2015 4:37:50 AM,              789,                  J1

  10/26/2015 6:13:56 PM,            276,                  L1

  10/26/2015 6:47:56 PM,            276,                    L2

  12/28/2015 6:26:59 AM,            999,                    M1

];


Capture.PNG