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