2 Replies Latest reply: Jan 11, 2013 7:18 AM by Sandro Krumbein RSS

    Custom grouping/ranking

    Sandro Krumbein

      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

        • Re: Custom grouping/ranking
          Fernando Suzuki

          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
              Sandro Krumbein

              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