Qlik Community

Third-Party Qlik Jobs

Area for people to learn more about current Qlik related job postings.

Highlighted
Sam1
New Contributor

Actual Hours - TIMEIN and TIMEOUT

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 idloginKey
XXX12/10/2018  9:10:00TimeIn
XXX12/10/2018  10:20:00Timeout
YYY12/11/2018  10:10:00TimeIn
YYY12/11/2018  10:20:00Timeout

 

Expected OUTPUT :- 

Emp idDateTimeInTimeoutAct hours
XXX12/10/20189:10:0010:20:0001:10:00
YYY12/11/201810:10:0010:20:0000:10:00

 

Regards,

Sam

3 Replies

Re: Actual Hours - TIMEIN and TIMEOUT

You need this in the script or front end?

Re: Actual Hours - TIMEIN and TIMEOUT

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
Esteemed Contributor III

Re: Actual Hours - TIMEIN and TIMEOUT

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:

Capture.PNG

Community Browser