Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hello!
I have a problem, which I think needs solving in the load script.
My data basically looks like this:
UserID | Timestamp | Session |
---|---|---|
1 | 1 | 1 |
1 | 5 | 2 |
1 | 6 | 2 |
1 | 20 | 3 |
1 | 21 | 3 |
2 | 1 | 1 |
2 | 2 | 1 |
2 | 3 | 1 |
2 | 10 | 2 |
2 | 11 | 2 |
Where as the UserID and Timestamp are the input data and Session already is what I want to calculate. The session far the sake of this example is defined as: events of a user, which are fired with a gap of no more than 3 timeunits between them.
My question now: How to calculate Session?
I have come as far as this:
Sessions:
LOAD
UserID,
Timestamp,
if (previous(UserID) <> UserID,
1,
if (Timestamp - previous(Timestamp) > 3, previous(Session) + 1, previous(Session)) AS Session
Resident
Events
Order by
UserID ASC,
Timestamp ASC
A similar approach would work in SQL, but it seems that Qlikviews previous() function can only reference the input table (Events), not the one it is creating in the moment (Sessions). Haven't found any alternatives and am lost now
Thank you!
Sandro
Hello Fernando and thank you for the response! 🙂
Your suggestion didn't quite work, but it gave me an idea. It seems like peek() does work in the table, that is being build (which was what I needed), but not on the underlying table. For that I still have to use previous()
Here is the solution (based on yours) that finally works:
Sessions:
LOAD
UserID,
Timestamp,
if (previous(UserID) <> UserID,
1,
peek(Session) + if (Timestamp - previous(Timestamp) > 3, 1, 0)) AS Session
Resident
Events
Order by
UserID ASC,
Timestamp ASC
Thank you! 🙂
Sandro
Hi Sandro,
you can use the Peek() function. Try this:
LOAD
UserID,
Timestamp,
if (peek('UserID') <> UserID,
1,
peek('Session') + if (Timestamp - peek('Timestamp') > 3, 1, 0) AS Session
Resident
Events
Order by
UserID ASC,
Timestamp ASC
Hope this helps you
Fernando
Hello Fernando and thank you for the response! 🙂
Your suggestion didn't quite work, but it gave me an idea. It seems like peek() does work in the table, that is being build (which was what I needed), but not on the underlying table. For that I still have to use previous()
Here is the solution (based on yours) that finally works:
Sessions:
LOAD
UserID,
Timestamp,
if (previous(UserID) <> UserID,
1,
peek(Session) + if (Timestamp - previous(Timestamp) > 3, 1, 0)) AS Session
Resident
Events
Order by
UserID ASC,
Timestamp ASC
Thank you! 🙂
Sandro