Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements
QlikView Fans! We’d love to hear from you.
Share your QlikView feedback with the product team… Click here to participate in our 5-minute survey.
Rules, plus terms and conditions, can be found here.
sandrok15
Contributor

Custom grouping/ranking

Hello!

I have a problem, which I think needs solving in the load script.

My data basically looks like this:

UserIDTimestampSession
111
152
162
1203
1213
211
221
231
2102
2112

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

Tags (3)
1 Solution

Accepted Solutions
sandrok15
Contributor

Re: Custom grouping/ranking

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

2 Replies
fosuzuki
Valued Contributor II

Re: Custom grouping/ranking

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

sandrok15
Contributor

Re: Custom grouping/ranking

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

Community Browser