Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

josecanalla
New Contributor III

Identify two consecutive dates and get hours difference between it

Hello, I have this data from a fingerprint device:

IDEmployee IDChecktimeChecktype
1126/10/17 08:00:00In
2226/10/17 08:10:00In
3326/10/17 08:11:00In
4226/10/1710:10:00Out
5126/10/17 12:10:00Out
6326/10/17 15:00:00Out
7.........

I want to know the hours worked for every employee in every day. For example I want to get:

Employee IDDateHours worked
126/10/176
226/10/178
3......

So I need to get from every "In" row the next "Out" row for the same Employee ID and get the difference in hours between two dates.

Any suggestion? Thanks!

15 Replies

Re: Identify two consecutive dates and get hours difference between it

Is this something you need in the script or front end? For front end, you might be able to do this

Dimension

Employee ID

Date

Expression

Interval(Only({<Checktype = {'Out'}>} Checktime) - Only({<Checktype = {'In'}>} Checktime), 'h')

josecanalla
New Contributor III

Re: Identify two consecutive dates and get hours difference between it

In case of employee has "n" In-Out in same day, for example work from 8-12 and then from 16-20 it works?

Re: Identify two consecutive dates and get hours difference between it

Can you elaborate on this?

josecanalla
New Contributor III

Re: Identify two consecutive dates and get hours difference between it

For example an employee can work on 20/10/17 from 08:00 to 12:00 (there is one In-Out) and then work from 16:00 to 20:00 (there is another In-Out) . The employee works 8 hours in 20/10/17 but in two intervals.

JustinDallas
Valued Contributor II

Re: Identify two consecutive dates and get hours difference between it

I'm tempted to take a crack at this, because time-oriented problems tend to be more common than we'd like, and their solutions tend to be more obnoxiously complex than one would think.

Questions:

  Can a shift overlap two days? i.e they Check-In at 11:55PM and Check-Out at 12:05AM the next day?

  Can there be Check-Ins and Outs that aren't paired up?  Like

     11:55 AM Check-In

     11:56 AM Check-In

     11:57 AM Check-Out

?

josecanalla
New Contributor III

Re: Identify two consecutive dates and get hours difference between it

  Can a shift overlap two days? i.e they Check-In at 11:55PM and Check-Out at 12:05AM the next day? Yes, can overlap two days.

  Can there be Check-Ins and Outs that aren't paired up? No. For one employee, there are one "In" and then one "Out".

Re: Identify two consecutive dates and get hours difference between it

This can work if you can flags in the script for every ins and outs....

simon_minifie
Contributor III

Re: Identify two consecutive dates and get hours difference between it

Quite new to all this myself, but wouldn't something like this work?

Temp_Data:

load * inline [

ID, EmployeeID, CheckTime, CheckType

1, 1, 26/10/17 08:00:00, In

2, 2, 26/10/17 08:10:00, In

3, 3, 26/10/17 08:11:00, In

4, 2, 26/10/17 10:10:00, Out

5, 1, 26/10/17 12:10:00, Out

6, 3, 26/10/17 15:00:00, Out

]

;

Data:

Load

*,

    Time(If(Peek(EmployeeID)=EmployeeID, Time-Peek(Time), Null()), 'hh:mm') as TimeDiff;

Load *,

Time(CheckTime) as Time;

Load

ID,

    EmployeeID,

    Timestamp#(CheckTime, 'DD/MM/YYYY hh:mm:ss') as CheckTime,

    CheckType

Resident Temp_Data

Order by EmployeeID;

Drop Table Temp_Data;

Capture.PNG

Thanks,

Simon

josecanalla
New Contributor III

Re: Identify two consecutive dates and get hours difference between it

I have a problem, if I do Date(checktime) from script or from frontend I get same date n times.

If I have checktimes 20/10/17 08:00:00 ; 20/10/17 09:00:00

I get two dates: 20/10/17 and 20/10/17 and when I select every one it references to one checktime...

Community Browser