How do I calculate number of hours worked from a single Clock_IN_OUT field? (Plz see table inside)

[#1]

Greetings everyone,

Given the table below, How would you calculate hours worked?

I have a single field which contains the timestamp of the clock in/out. so the odd records are the "clock in"s and the even ones are the "clock out"s. an employee may clock in and out a number of times  during the shift. I need to calculate the time between the 1st clock_in_out and the 2nd clock_in_out + time between 3rd and 4th clock_in_out and so on (skipping calculating the time between 2nd and 3rd clock_in_out timestamp for example)

My goal is to have a single row showing the Shif Date| branch ID | Employee ID| Number of hours worked

BRANCH_ID	EMPLOYEE_ID	CLOCK_IN_OUT	SHIFT_DAY
24	413481137	6/17/2015 15:01	20150617
24	413481137	6/18/2015 3:36	20150617
24	516151270	6/17/2015 11:36	20150617
24	516151270	6/17/2015 19:26	20150617
24	963899270	6/17/2015 9:36	20150617
24	963899270	6/17/2015 15:14	20150617
24	963899270	6/17/2015 22:15	20150617
24	963899270	6/18/2015 3:35	20150617
24	1072004539	6/17/2015 9:37	20150617
24	1072004539	6/17/2015 15:34	20150617
24	1072004539	6/17/2015 20:11	20150617
24	1072004539	6/18/2015 3:36	20150617
24	1136117204	6/17/2015 17:04	20150617
24	1136117204	6/18/2015 3:20	20150617
24	2128095154	6/17/2015 9:37	20150617
24	2128095154	6/17/2015 15:33	20150617
24	2128095154	6/17/2015 20:11	20150617
24	2128095154	6/18/2015 3:35	20150617
24	2174647422	6/17/2015 18:38	20150617
24	2174647422	6/18/2015 3:21	20150617
24	2527062199	6/17/2015 15:01	20150617
24	2527062199	6/18/2015 3:35	20150617
24	2779657312	6/17/2015 15:01	20150617
24	2779657312	6/18/2015 3:36	20150617
24	3033275348	6/17/2015 15:01	20150617
24	3033275348	6/18/2015 3:35	20150617

[swuehl · Accepted solution]

Set TimestampFormat ='M/D/YYYY hh:mm';

LOAD BRANCH_ID,
  EMPLOYEE_ID,
  SHIFT_DAY,
  interval(sum(TimeWorked)) as TotalTimeWorked
GROUP BY BRANCH_ID, EMPLOYEE_ID, SHIFT_DAY;
LOAD BRANCH_ID,
    EMPLOYEE_ID,
    CLOCK_IN_OUT,
    SHIFT_DAY,
    if(even(RecNo()), CLOCK_IN_OUT - previous(CLOCK_IN_OUT)) as TimeWorked
FROM
[https://community.qlik.com/thread/169076]
(html, codepage is 1252, embedded labels, table is @1);

[#3]

Thank you for your speedy reply I will try it out.

[swuehl]

This should work as long as the input table comes in sorted like in your sample data above.

[svenkita]

Attaching a Qvw file.

Hope this Helps

Tmp:
Load *, Date(Date#(SHIFT_DAY,'YYYYMMDD'),'DD/MM/YYYY') as NewShiftDate,
Timestamp(Timestamp#(CLOCK_IN_OUT,'MM/DD/YYYY hh:mm'),'DD/MM/YYYY hh:mm') as NewClockInOut,
Num(Timestamp(Timestamp#(CLOCK_IN_OUT,'MM/DD/YYYY hh:mm'),'DD/MM/YYYY hh:mm')) as NewClockInOutNum;

LOAD * INLINE [
BRANCH_ID, EMPLOYEE_ID, CLOCK_IN_OUT, SHIFT_DAY
24, 413481137, 6/17/2015 15:01, 20150617
24, 413481137, 6/18/2015 3:36, 20150617
24, 516151270, 6/17/2015 11:36, 20150617
24, 516151270, 6/17/2015 19:26, 20150617
24, 963899270, 6/17/2015 9:36, 20150617
24, 963899270, 6/17/2015 15:14, 20150617
24, 963899270, 6/17/2015 22:15, 20150617
24, 963899270, 6/18/2015 3:35, 20150617
24, 1072004539, 6/17/2015 9:37, 20150617
24, 1072004539, 6/17/2015 15:34, 20150617
24, 1072004539, 6/17/2015 20:11, 20150617
24, 1072004539, 6/18/2015 3:36, 20150617
24, 1136117204, 6/17/2015 17:04, 20150617
24, 1136117204, 6/18/2015 3:20, 20150617
24, 2128095154, 6/17/2015 9:37, 20150617
24, 2128095154, 6/17/2015 15:33, 20150617
24, 2128095154, 6/17/2015 20:11, 20150617
24, 2128095154, 6/18/2015 3:35, 20150617
24, 2174647422, 6/17/2015 18:38, 20150617
24, 2174647422, 6/18/2015 3:21, 20150617
24, 2527062199, 6/17/2015 15:01, 20150617
24, 2527062199, 6/18/2015 3:35, 20150617
24, 2779657312, 6/17/2015 15:01, 20150617
24, 2779657312, 6/18/2015 3:36, 20150617
24, 3033275348, 6/17/2015 15:01, 20150617
24, 3033275348, 6/18/2015 3:35, 20150617
];

NoConcatenate
New:
Load *, RowNo( ), If(Mod(RowNo( ),2)=1,'In','Out') as InOut
Resident Tmp
Order by BRANCH_ID, EMPLOYEE_ID,NewShiftDate,NewClockInOutNum;

Drop Table Tmp;

[#6]

Write a query (T-Sql) before loading into QVD which give result in

BRANCH_ID	EMPLOYEE_ID	CLOCK_IN CLOCK_OUT SHIFT_DAY
24	413481137	6/17/2015 15:01  6/18/2015 3:36  20150617

[hariprasadqv]

I Agree with swuehl.

[vivek_niti]

Hi,

Try this.

tab1:

LOAD *,

  Timestamp#(CLOCK_IN_OUT,'MM/DD/YYYY hh:mm') AS CLOCK_IN_OUT_;

LOAD * INLINE [   

    BRANCH_ID, EMPLOYEE_ID, CLOCK_IN_OUT, SHIFT_DAY

    24, 413481137, 6/17/2015 15:01, 20150617

    24, 413481137, 6/18/2015 3:36, 20150617

    24, 516151270, 6/17/2015 11:36, 20150617

    24, 516151270, 6/17/2015 19:26, 20150617

    24, 963899270, 6/17/2015 9:36, 20150617

    24, 963899270, 6/17/2015 15:14, 20150617

    24, 963899270, 6/17/2015 22:15, 20150617

    24, 963899270, 6/18/2015 3:35, 20150617

    24, 1072004539, 6/17/2015 9:37, 20150617

    24, 1072004539, 6/17/2015 15:34, 20150617

    24, 1072004539, 6/17/2015 20:11, 20150617

    24, 1072004539, 6/18/2015 3:36, 20150617

    24, 1136117204, 6/17/2015 17:04, 20150617

    24, 1136117204, 6/18/2015 3:20, 20150617

    24, 2128095154, 6/17/2015 9:37, 20150617

    24, 2128095154, 6/17/2015 15:33, 20150617

    24, 2128095154, 6/17/2015 20:11, 20150617

    24, 2128095154, 6/18/2015 3:35, 20150617

    24, 2174647422, 6/17/2015 18:38, 20150617

    24, 2174647422, 6/18/2015 3:21, 20150617

    24, 2527062199, 6/17/2015 15:01, 20150617

    24, 2527062199, 6/18/2015 3:35, 20150617

    24, 2779657312, 6/17/2015 15:01, 20150617

    24, 2779657312, 6/18/2015 3:36, 20150617

    24, 3033275348, 6/17/2015 15:01, 20150617

    24, 3033275348, 6/18/2015 3:35, 20150617

];

tab2:

NOCONCATENATE

LOAD

  SHIFT_DAY,

  BRANCH_ID,

  EMPLOYEE_ID,

  CLOCK_IN_OUT_ AS CLOCK_IN_OUT

RESIDENT tab1

ORDER BY SHIFT_DAY,BRANCH_ID, EMPLOYEE_ID, CLOCK_IN_OUT_ DESC;

DROP TABLE tab1;

tab3:

NOCONCATENATE

LOAD SHIFT_DAY,

  BRANCH_ID,

  EMPLOYEE_ID,

  CLOCK_IN_OUT,

  If(SHIFT_DAY = Peek(SHIFT_DAY) AND BRANCH_ID = Peek(BRANCH_ID) AND EMPLOYEE_ID = Peek(EMPLOYEE_ID), Peek(CLOCK_IN_OUT),Null()) AS Out_Time

RESIDENT tab2; 

DROP TABLE tab2;

tab4:

NOCONCATENATE

LOAD *,

  Interval(Out_Time-In_Time,'hh:mm') AS Interval_Time;

LOAD

  SHIFT_DAY,

  BRANCH_ID,

  EMPLOYEE_ID,

  CLOCK_IN_OUT AS In_Time,

  Out_Time

RESIDENT tab3;

DROP TABLE tab3;