topic Count events that happen within certain timeframe in QlikView

Count events that happen within certain timeframe

Wed, 25 Nov 2020 16:16:04 GMT

I am quite new to Qlikview and have been struggling with this issue for a while now.

I have the following example of data:

Corr ID Date/time Event

22 2016-04-22 11:00:22 xyz

23 2016-04-22 11:00:38 abc

22 2016-04-22 11:05:10 xyz

23 2016-04-22 11:20:23 abc

What I am trying to accomplish is to count the number of occurrences of a certain event with the same Corr ID where it has been less than (in this example) 5 minutes between the two, i.e. the one above with Corr ID 22 should count as one and 23 should not.

Re: Count events that happen within certain timeframe

sunny_talwar — Fri, 22 Apr 2016 13:53:09 GMT

See if this helps:

TABLE:

LOAD * Inline [

Corr ID, Date/time, Event

22, 2016-04-22 11:00:22, xyz

23, 2016-04-22 11:00:38, abc

22, 2016-04-22 11:05:10, xyz

23, 2016-04-22 11:20:23, abc

];

FinalTable:

LOAD *,

If(Time < MakeTime(0, 5), 1, 0) as Flag;

LOAD *,

Interval(If(Peek('Corr ID') = [Corr ID], [Date/time] - Peek('Date/time')), 'hh:mm') as Time

Resident TABLE

Order By [Corr ID], [Date/time];

DROP Table TABLE;

Re: Count events that happen within certain timeframe

MarcoWedel — Sun, 24 Apr 2016 00:41:01 GMT

Hi,

maybe one solution could be:

hope this helps

regards

Marco

Re: Count events that happen within certain timeframe

Tue, 26 Apr 2016 13:12:33 GMT

This did the trick. Thank you very much!