Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi -
I am trying to find actual hours between timeIN and timeOUT by "Emp id" and "date" while loading. I tried Generic load and Transpose option but not working as expected. Please suggest any solution. Thank you.
INPUT :-
Emp id | login | Key |
XXX | 12/10/2018 9:10:00 | TimeIn |
XXX | 12/10/2018 10:20:00 | Timeout |
YYY | 12/11/2018 10:10:00 | TimeIn |
YYY | 12/11/2018 10:20:00 | Timeout |
Expected OUTPUT :-
Emp id | Date | TimeIn | Timeout | Act hours |
XXX | 12/10/2018 | 9:10:00 | 10:20:00 | 01:10:00 |
YYY | 12/11/2018 | 10:10:00 | 10:20:00 | 00:10:00 |
Regards,
Sam
For script based solution... try this
Table: LOAD Emp_id, Date(Floor(login)) as Date, login, Key; LOAD * INLINE [ Emp_id, login, Key XXX, 12/10/2018 9:10:00, TimeIn XXX, 12/10/2018 10:20:00, Timeout YYY, 12/11/2018 10:10:00, TimeIn YYY, 12/11/2018 10:20:00, Timeout ]; TempTable: LOAD DISTINCT Emp_id, Date, login as TimeIn Resident Table Where Key = 'TimeIn'; Left Join (TempTable) LOAD DISTINCT Emp_id, Date, login as Timeout Resident Table Where Key = 'Timeout'; FinalTable: LOAD Emp_id, Date, TimeIn, Timeout, Interval(Timeout - TimeIn, 'h:mm:ss') as Act_Hours Resident TempTable; DROP Tables Table, TempTable;
Try this?
Generic
LOAD Empid, Key, Timestamp#(login, 'DD/MM/YYYY hh:mm:ss') as Logintime;
LOAD * INLINE [
Empid, login, Key
XXX, 12/10/2018 9:10:00, TimeIn
XXX, 12/10/2018 10:20:00, Timeout
YYY, 12/11/2018 10:10:00, TimeIn
YYY, 12/11/2018 10:20:00, Timeout
];
Using straight table add Empid, TimeIn, TimeOut in your dimensions and
expr as : = Interval(Timeout - TimeIn, 'hh:mm:ss')
You should see below: