Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

Announcements
Make your voice heard! Participate in the 2020 Wisdom of Crowds® Survey. BEGIN SURVEY
Highlighted
Employee
Employee

Counting events withing range

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

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

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:

TimestampEventB's Within 5s
20180831T094020.141-0400A0
20180831T094021.500-0400B0
​20180831T094023.411-0400B1
20180831T094024.570-0400A2
20180831T094026.570-0400A1

How would I express this?

2 Replies
Highlighted

Re: Counting events withing range

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

Highlighted
MVP
MVP

Re: Counting events withing range

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-0400A0
20180831T094021.500-0400B0
20180831T094023.411-0400B1
20180831T094024.570-0400A2
20180831T094026.570-0400A1