Actual Hours - TIMEIN and TIMEOUT

[Sam1]

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

[sunny_talwar]

You need this in the script or front end?

[sunny_talwar]

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;

[vishsaggi]

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: