Detect longest pause between start/end time stamps.

[OysteinT]

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)

EmpID	StampID	StartDateTime	EndDateTime	Department	Rest Hours
ID1	77776	2020-06-25 06:00:00	2020-06-25 14:00:00	A123
ID1	77777	2020-06-26 12:00:00	2020-06-26 21:00:00	A123	22
ID1	66577	2020-06-27 06:00:00	2020-06-27 14:00:00	A123	9

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)

EmpID	StampID	StartDateTime	EndDateTime	Department	Rest Hours
ID1	55555	2020-06-25 06:00:00	2020-06-25 08:00:00	A132
ID1	55556	2020-06-25 11:00:00	2020-06-25 12:00:00	A123	3
ID1	55557	2020-06-25 12:00:00	2020-06-25 15:00:00	A222	0
ID1	55558	2020-06-25 20:00:00	2020-06-25 22:00:00	A123	5
ID1	88888	2020-06-26 10:00:00	2020-06-26 16:00:00	A132	12
ID1	88878	2020-06-26 19:00:00	2020-06-26 19:30:00	A555	3
ID1	99978	2020-06-27 06:00:00	2020-06-27 08:00:00	A123	10,5
ID1	55664	2020-06-28 21:00:00	2020-06-29 04:00:00	A123	37
ID1	12345	2020-06-29 18:00:00	2020-06-29 06:00:00	A123	14

(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 🙂 

[Kushal_Chawda · Accepted solution]

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]

Awesome, this works perfectly.