Skip to main content
Announcements
Product Release Webinar: Qlik Insider airing December 6! REGISTER TODAY!
cancel
Showing results for 
Search instead for 
Did you mean: 
kakani87
Specialist
Specialist

Break Time Calculation

Dear All,

I'm having employee data with me contains Name,In & Out Time, Date and a flag InOut 0 as In 1 as Out now i want to display the break time for every  Out and  In time calculation

let say i'm out on  01:30 pm and in on 02:00 pm so break time is 30 mins.

Can any one help me to achieve this.

Attached  sample data for reference purpose

PFA.

Regards,

Kakani.


1 Solution

Accepted Solutions
pradosh_thakur
Master II
Master II

Like this

XsDetails:

LOAD tran_date,

     EmpID,

     EmpName,

     InOut,

      Time

FROM

Emp_XsDetails.xls

(biff, embedded labels, table is Sheet1$);

NoConcatenate

XsDetails1:

load * ,

if(Previous(EmpID)=EmpID and InOut =0,Interval(Time-PEEK(Time),'mm'),'N/A') as [break in mins]

Resident

XsDetails Order by tran_date,

     EmpID,

     EmpName,

 

     Time;

    

     drop Table XsDetails;

Capture.PNG

Learning never stops.

View solution in original post

4 Replies
PrashantSangle

can you explain your sample data? provide required output w.r.t your data?

to find time / date difference you can use interval()

see below thread

Interval - script and chart function ‒ QlikView

Regards,

Prashant Sangle

Great dreamer's dreams never fulfilled, they are always transcended.
kakani87
Specialist
Specialist
Author

Hi Prashant,

This is employee access details contains date , Name and access details like each and every swipe where InOut is a flag '0' entry is swipe in , flag '1' entry  is swipeout

as follows.

now for emp chitra S out time is 11:12:18:AM ans In time is 11:30:06 AM break time of her is around 18 mins

my requirement is for every out and in of an employee should be displayed 

XsDetails.png

pradosh_thakur
Master II
Master II

Like this

XsDetails:

LOAD tran_date,

     EmpID,

     EmpName,

     InOut,

      Time

FROM

Emp_XsDetails.xls

(biff, embedded labels, table is Sheet1$);

NoConcatenate

XsDetails1:

load * ,

if(Previous(EmpID)=EmpID and InOut =0,Interval(Time-PEEK(Time),'mm'),'N/A') as [break in mins]

Resident

XsDetails Order by tran_date,

     EmpID,

     EmpName,

 

     Time;

    

     drop Table XsDetails;

Capture.PNG

Learning never stops.
kakani87
Specialist
Specialist
Author

Thank you Pradosh for spending your valuable time to help me out of this scenario.