Counting events withing range

[Øystein_Kolsrud]

I have a case where I have a number of events of the following format:

Timestamp	Event
20180831T094020.141-0400	A
20180831T094021.500-0400	B
​20180831T094023.411-0400	B
20180831T094024.570-0400	A
20180831T094026.570-0400	A

I want to create an expression that for each row of the table counts the number of events of type B that occurred within 5 seconds from the current event. So the resulting table would look like this:

Timestamp	Event	B's Within 5s
20180831T094020.141-0400	A	0
20180831T094021.500-0400	B	0
​20180831T094023.411-0400	B	1
20180831T094024.570-0400	A	2
20180831T094026.570-0400	A	1

How would I express this?

[marinadorcassio1]

Hi,

I think the idea is :

- Create a new field that is a real timestamp IN SECONDS understood by Qlik in your script as TimestampFormatted

- Then, your expression :

count( {< Event={'B'}, TimestampFormatted={" >= $(=[TimestampFormatted] + 5)  "} >} [Timestamp])

Hop it helps,

Marina

[swuehl]

Maybe using an AsOf table, something like

INPUT:

LOAD Timestamp,

     Event

FROM

[https://community.qlik.com/thread/312828]

(html, utf8, embedded labels, table is @1);

TimeAsOfTMP:

LOAD DISTINCT Timestamp

Resident INPUT;

JOIN

LOAD Timestamp as TimestampAsOf

Resident TimeAsOfTMP;

TimeAsOf:

NoConcatenate

LOAD Timestamp, TimestampAsOf, Timestamp = TimestampAsOf as Flag

Resident TimeAsOfTMP where (TimestampAsOf-Timestamp) <= MakeTime(0,0,5) and  (TimestampAsOf-Timestamp)>=0;

DROP TABLE TimeAsOfTMP;

and then:

TimestampAsOf	Only({<Flag = {'-1'} >} Event)	Count({<Flag = {'0'}, Event = {'B'} >} Event)
20180831T094020.141-0400	A	0
20180831T094021.500-0400	B	0
20180831T094023.411-0400	B	1
20180831T094024.570-0400	A	2
20180831T094026.570-0400	A	1