Latest values based on time stamp

Mon, 11 Jan 2016 20:18:12 GMT

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)

Re: Latest values based on time stamp

sunny_talwar — Mon, 11 Jan 2016 20:35:26 GMT

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

];

topic Latest values based on time stamp in QlikView

Latest values based on time stamp

Re: Latest values based on time stamp