Count distinct access logs with a 1 hour cooldown??

[Diego_780]

Hello Qlik pros,

We have a log of user access to different website functionalities, with date, time, user ID, functionality ID, and other data. We want to filter these access logs by applying a specific rule that considers both the user and the functionality they access: first we take the earliest access of a specific user to a specific functionality (in this case, user 'di033samca07092021' accessing functionality '569' at 9:02), and then we ignore all subsequent accesses by that same user to that same functionality during the next hour. We only want to keep the first access and the next access that occurs after that one-hour 'cooldown' period (in this case the one at 10:41).

In other words, we're tracking unique combinations of user ID and functionality ID, and applying a one-hour cooldown filter to each combination. This means a user could access different functionalities within that hour and those would be counted separately, but repeated accesses to the same functionality by the same user within an hour are filtered out.

The table it´s the following, it´s already filtered with only one functionality ID and user ID but this table is much bigger with different combinations for each user, funcionality ID  and date and times:

FECHA HORA ID_FUNCIONALIDAD UID_USUARIO ID 07/02/2025 10:41 569 di033samca07092021 166039361 07/02/2025 10:41 569 di033samca07092021 166039335 07/02/2025 09:25 569 di033samca07092021 166025720 07/02/2025 09:12 569 di033samca07092021 166024675 07/02/2025 09:02 569 di033samca07092021 166024079 07/02/2025 09:02 569 di033samca07092021 166024078

Maybe I´m not very clear, but it´s driving me crazy.

Thanks in regard,

Diego.

[Clement15 · Accepted solution]

Hello,

This gives you a flag that you can call in an analysis set to select the desired values.
Does this work for you?

Data:
Load
*,
Timestamp(date(FECHA) & ' ' & Time(HORA)) as Timestamp
Inline [
FECHA,HORA,ID_FUNCIONALIDAD,UID_USUARIO,ID
7/2/2025,10:41,569,di033samca07092021,166039361
7/2/2025,10:41,569,di033samca07092021,166039335
7/2/2025,09:25,569,di033samca07092021,166025720
7/2/2025,09:12,569,di033samca07092021,166024675
7/2/2025,09:02,569,di033samca07092021,166024079
7/2/2025,09:02,569,di033samca07092021,166024078
];

NoConcatenate

Temp:
LOAD
Timestamp,
FECHA,
HORA,
ID_FUNCIONALIDAD,
UID_USUARIO,
ID
Resident Data
ORDER BY UID_USUARIO, ID_FUNCIONALIDAD, Timestamp, ID;

Final:
NoConcatenate
LOAD
FECHA,
HORA,
ID_FUNCIONALIDAD,
UID_USUARIO,
ID,
Timestamp,
If(
(UID_USUARIO = Peek('UID_USUARIO') and
ID_FUNCIONALIDAD = Peek('ID_FUNCIONALIDAD') and
(Timestamp - Peek('LastTimestamp')) < 1/24),
0,
1
) as KeepFlag,
If(
(UID_USUARIO = Peek('UID_USUARIO') and
ID_FUNCIONALIDAD = Peek('ID_FUNCIONALIDAD') and
(Timestamp - Peek('LastTimestamp')) < 1/24),
Peek('LastTimestamp'),
Timestamp
) as LastTimestamp
Resident Temp
ORDER BY UID_USUARIO, ID_FUNCIONALIDAD, Timestamp, ID;

DROP Table Temp;
DROP Table Data;

[Diego_780]

Hello Clement15,

Yeah that´s what i was looking for! Thank you so much!

May you explain to me the code a little? 

I don´t understand the If condition fully, I thought that if you wanted to Peek a dimension, it should have been already created (LastTimestam) and don´t understand the math of < 1/24.

If you could explain it to me i would be truly grateful as to understand the working code.

Again, thanks a lot.

[Clement15]

In Qlik, the Peek() function lets you access a field’s value from the previous row during the load process, even if that field (like LastTimestamp) is being created on the fly. This means you don't need to pre-create LastTimestamp in your source data.

As each record is processed, Peek() retrieves the values of UID_USUARIO, ID_FUNCIONALIDAD, and LastTimestamp from the previous row. The script then compares the current record’s Timestamp with this LastTimestamp. Since Qlik represents dates in days, 1 hour is equivalent to 1/24 of a day. Thus, the condition (Timestamp - Peek('LastTimestamp')) < 1/24 checks if less than an hour has passed since the last kept access for the same user-functionality combination.

If the difference is less than 1/24, the record is marked with a KeepFlag of 0 (ignored) and LastTimestamp remains unchanged. Otherwise, the record is kept (KeepFlag = 1) and LastTimestamp is updated to the current Timestamp. This approach effectively filters out repeated accesses within an hour for each combination, all while generating and updating LastTimestamp dynamically during the load.