Count events that happen within certain timeframe

[#1]

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.

[sunny_talwar · Accepted solution]

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;

[MarcoWedel]

Hi,

maybe one solution could be:

hope this helps

regards

Marco

[#4]

This did the trick. Thank you very much!