topic Re: Find time difference in minutes between end time and begin time of consecutive IDs in QlikView

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

andrewmo — Tue, 09 May 2017 13:23:00 GMT

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

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

m_woolf — Tue, 09 May 2017 14:26:41 GMT

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

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

Anil_Babu_Samineni — Tue, 09 May 2017 14:29:06 GMT

Please provide more information how data looks like?

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

andrewmo — Mon, 15 May 2017 17:24:27 GMT

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

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

andrewmo — Mon, 15 May 2017 17:26:55 GMT

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

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

Anonymous — Mon, 15 May 2017 17:37:08 GMT

is it sorted by ID, then by Date?

are there overlapping Date Sections?

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

andrewmo — Mon, 15 May 2017 17:40:22 GMT

Currently sorted by date/time

There is a possibility of overlapping date sections

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

Anonymous — Mon, 15 May 2017 17:44:03 GMT

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

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

andrewmo — Mon, 15 May 2017 17:46:29 GMT

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

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

Anonymous — Mon, 15 May 2017 17:54:14 GMT

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

try:

LOAD

LOG,

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

resident TIMES;

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

andrewmo — Mon, 15 May 2017 17:58:30 GMT

I tried this but the DELTA returns 00

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

Anonymous — Mon, 15 May 2017 18:20:18 GMT

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;

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

andrewmo — Tue, 16 May 2017 14:54:45 GMT

That appears to do the trick...thanks much!