Skip to main content
Announcements
Qlik Introduces a New Era of Visualization! READ ALL ABOUT IT
cancel
Showing results for 
Search instead for 
Did you mean: 
andrewmo
Creator
Creator

Find time difference in minutes between end time and begin time of consecutive IDs

Hello

Can anyone recommend a way to calculate the time difference between the end time of one event and the beginning time of the next event?

For example

LOG 1234

Start 7:20

End 7:40

LOG 5678

Start 7:45

End 8:00

The gap between LOG 1234 end and LOG 5678 start is 5 minutes.

Thanks in advance

1 Solution

Accepted Solutions
Anonymous
Not applicable

next try

SET TimestampFormat='M/D/YYYY hh:mm[:ss][.fff]';

TIMES:

LOAD * Inline [
LOG, In, Out
1234, 5/1/2017 07:20, 5/1/2017 07:40
4567, 5/1/2017 07:50, 5/1/2017 08:20
]
;

LOAD
LOG,
Interval(if(IsNull(Peek('Out')),0,Rangesum(-Peek('Out'),In)),'m') as DELTA,
In,
Out

resident TIMES;

DROP Table TIMES;

View solution in original post

12 Replies
m_woolf
Master II
Master II

You can use the peek() function in your script.

Anil_Babu_Samineni

Please provide more information how data looks like?

Best Anil, When applicable please mark the correct/appropriate replies as "solution" (you can mark up to 3 "solutions". Please LIKE threads if the provided solution is helpful
andrewmo
Creator
Creator
Author

Here is an example of how the data looks:

TIMES:

LOAD * Inline [

LOG, In, Out

1234, 5/1/2017 07:20, 5/1/2017 07:40

4567, 5/1/2017 07:50, 5/1/2017 08:20

]

I would expect to see a 10 minute difference between the Out time of LOG 1234 and the In time of LOG 4567

Thanks

andrewmo
Creator
Creator
Author

I thought this would work but I was unable to get the results I expected; I assume it is because I do not fully understand the peek function.

TIMES:

LOAD * Inline [

LOG, In, Out

1234, 5/1/2017 07:20, 5/1/2017 07:40

4567, 5/1/2017 07:50, 5/1/2017 08:20

]

Would the logic for the above load statement be:

LOAD LOG

,interval(peek(Out)-In,'mm') as Gap

RESIDENT TIMES;

Thanks again

Anonymous
Not applicable

is it sorted by ID, then by Date?

are there overlapping Date Sections?

andrewmo
Creator
Creator
Author

Currently sorted by date/time

There is a possibility of overlapping date sections

Anonymous
Not applicable

so you like to see the difference, if the "LOG" changes?

andrewmo
Creator
Creator
Author

No. I already have the times for each log. Want to see the gap in minutes between each log.

Anonymous
Not applicable

I suppose, that you have dual()-timestamps:

try:

LOAD

LOG,

Interval(Rangesum(-1*Peek('In'),Out),'mm' ) as DELTA

resident TIMES;