topic Re: Custom grouping/ranking in QlikView

Custom grouping/ranking

Anonymous — Fri, 11 Jan 2013 08:16:17 GMT

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,

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

Re: Custom grouping/ranking

fosuzuki — Fri, 11 Jan 2013 10:39:47 GMT

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

Re: Custom grouping/ranking

Anonymous — Fri, 11 Jan 2013 12:18:32 GMT

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: