Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements
Modernize Your QlikView Deployment webinar, Nov. 3rd. REGISTER
cancel
Showing results for 
Search instead for 
Did you mean: 
Highlighted
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
Highlighted
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
Highlighted
Master II
Master II

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

Highlighted

Please provide more information how data looks like?

Before develop something, think If placed (The Right information | To the right people | At the Right time | In the Right place | With the Right context)
Highlighted
Creator
Creator

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

Highlighted
Creator
Creator

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

Highlighted
Anonymous
Not applicable

is it sorted by ID, then by Date?

are there overlapping Date Sections?

Highlighted
Creator
Creator

Currently sorted by date/time

There is a possibility of overlapping date sections

Highlighted
Anonymous
Not applicable

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

Highlighted
Creator
Creator

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

Highlighted
Anonymous
Not applicable

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

try:

LOAD

LOG,

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

resident TIMES;