Skip to main content
Announcements
Global Transformation Awards! Applications are now open. Submit Entry
cancel
Showing results for 
Search instead for 
Did you mean: 
OysteinT
Contributor III
Contributor III

Detect longest pause between start/end time stamps.

Hi

 

I'm looking at generating data based on time stamps. I'm looking at measuring the amount of time between a Stop and a Start (to see how long there has been a pause) on the next day per EmpID. Primarily to check that there has been minmum X numbers of hours.

Data set example (simple) (column Rest is one I've added to visualize the figure I'm looking for, and which I'd ideally want as a scripted column, not Set Analytics. It also refers to the diff between StartDateTime  and previous EndDateTime, hence no number for first column)

EmpIDStampIDStartDateTimeEndDateTimeDepartmentRest Hours 
ID1777762020-06-25 06:00:002020-06-25 14:00:00A123 
ID1777772020-06-26 12:00:002020-06-26 21:00:00A12322
ID1665772020-06-27 06:00:002020-06-27 14:00:00A1239

 

So for example in the example above the it measures the EndDateTime of the previous day to the StartDayTime of the next day. This isn't rocket science and something I can solve pretty easily.

My question is how to handle example with multiple stamps per day. Here I really want to check between each End and Start for each ID within each instance. Each StampID is unique and only belowns to one EmpID.

Advanced example (again, the Rest column is not in the data set, but visualization of the figure I'm looking for)

EmpIDStampIDStartDateTimeEndDateTimeDepartmentRest Hours
ID1555552020-06-25 06:00:002020-06-25 08:00:00A132 
ID1555562020-06-25 11:00:002020-06-25 12:00:00A1233
ID1555572020-06-25 12:00:002020-06-25 15:00:00A2220
ID1555582020-06-25 20:00:002020-06-25 22:00:00A1235
ID1888882020-06-26 10:00:002020-06-26 16:00:00A13212
ID1888782020-06-26 19:00:002020-06-26 19:30:00A5553
ID1999782020-06-27 06:00:002020-06-27 08:00:00A12310,5
ID1556642020-06-28 21:00:002020-06-29 04:00:00A12337
ID1123452020-06-29 18:00:002020-06-29 06:00:00A12314


(The department will just be a reporting dimension)
Is there an somewhat easy way to do this? There is no real way to use the StampIDs to check the next one, they are unique but the next number might not be on that EmpID. 

I understand the logic needed (While EmpID = ID1 select NextStartTimeDate, Rest = NextStartTimeDate - CurrentEndTimeDate) but now how to do it 🙂 

1 Solution

Accepted Solutions
Kushal_Chawda

Make sure that your Date fields are in proper timestamp format Then you can try below

Data:
LOAD
    EmpID,
    StampID,
    StartDateTime,
    EndDateTime,
    Department,
    "Rest Hours"
FROM [lib://Qlik]
(html, utf8, embedded labels, table is @2);

New:
NoConcatenate
Load *,
     peek(EndDateTime) as End,
     if(Peek(EmpID)=EmpID,
     round((StartDateTime-peek(EndDateTime))*24,0.01)) as Rest_Hour
Resident Data
Order by EmpID,StartDateTime;

Drop Table Data;

 

View solution in original post

2 Replies
Kushal_Chawda

Make sure that your Date fields are in proper timestamp format Then you can try below

Data:
LOAD
    EmpID,
    StampID,
    StartDateTime,
    EndDateTime,
    Department,
    "Rest Hours"
FROM [lib://Qlik]
(html, utf8, embedded labels, table is @2);

New:
NoConcatenate
Load *,
     peek(EndDateTime) as End,
     if(Peek(EmpID)=EmpID,
     round((StartDateTime-peek(EndDateTime))*24,0.01)) as Rest_Hour
Resident Data
Order by EmpID,StartDateTime;

Drop Table Data;

 

OysteinT
Contributor III
Contributor III
Author

Awesome, this works perfectly.

 

OysteinT_0-1594883747459.png