Custom grouping/ranking

[Anonymous]

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

[fosuzuki]

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

[Anonymous · Accepted solution]

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