4 Replies Latest reply: Jan 30, 2012 11:55 AM by Ivan Troisi RSS

    Group

    Ivan Troisi

      Good day, can you help me?.

       

      I have the following table:

       

      user_idbug_iddate_modified
      3336151201550061
      3336151201550061
      3336151201550061
      3336151201550061
      3336151201550061
      3336151201550061
      3336151201550061
      3336151201550239
      3336151201550259
      3336151201550259
      236151201552220
      236151201552228
      3436151204037843
      3436151204037846
      236151204048000
      236151204048000
      236151204048033
      236151204048033
      236151204138273
      236151208202609
      236151208202609

       

      I must subtract the date greater with the minor by user_id, but respecting the order.

       

       

      I need the following:

       

      user_idbug_iddate_modified
      333615198
      236158
      3436153
      236154154609

       

      Regards!.

       

      Ivan. 

        • Re: Group
          Stefan Wühl

          Maybe like attached?

           

          I am not sure what you are doing with bug_id, but since you said "by user_id", it seems to me that you don't need to group by or check bug_id.

           

          I used this load statement:

           

          LOAD recno() as RecID,

               user_id,

               bug_id,

               date_modified,

               if(RecNo()=1,1,if(peek(user_id)=user_id,peek( user_group_id), peek(user_group_id)+1)) as user_group_id

          FROM

          [http://community.qlik.com/thread/46220?tstart=0]

          (html, codepage is 1252, embedded labels, table is @1);

           

          creating a user_group_id with e.g. two distinct ids for user_id 2.

           

          Then I created a chart with dimensions user_group_id, user_id, bug_id (and I've hided the user_group_id in presentation tab), and as expression:

           

          =max(date_modified)-min(date_modified)

           

          Hope this helps,

          Stefan

            • Group
              Ivan Troisi

              Great!!..

               

              bug_id is also a field to grouping.

               

              user_idbug_iddate_modified
              3336151201550061
              3336151201550061
              3336151201550061
              3336151201550061
              3336151201550061
              3336151201550061
              3336151201550061
              3336151201550239
              3336151201550259
              3336151201550259
              236151201552220
              236151201552228
              3436151204037843
              3436151204037846
              236151204048000
              236201204048000
              536201204048033
              536201204048033
              536201204138273
              236201208202609
              236201208202609

               

              How he added this field in the band?.

               

              Thank you very much!!

               

              Ivan. 

              • Re: Group
                Stefan Wühl

                Just extend the if() clause:

                 

                LOAD recno() as RecID,

                     user_id,

                     bug_id,

                     date_modified,

                     if(RecNo()=1,1,

                     if(peek(user_id)=user_id and peek(bug_id)=bug_id,peek( user_group_id), peek(user_group_id)+1)) as user_group_id

                FROM

                [http://community.qlik.com/thread/46220?tstart=0]

                (html, codepage is 1252, embedded labels, table is @3);

                 

                edit:

                You need to uncheck Suppress Zero Values in presentation tab to see all user_group_id values, also the ones with zero difference in date_modified.