4 Replies Latest reply: May 18, 2015 2:10 PM by Xavier Retaillaud RSS

    increment field on dimension changes

    Xavier Retaillaud

      Hi Qlikers,

       

      Table1:

      LOAD * Inline [
      Date, Employee, Event
      01/01/2015, Pierre, A
      02/01/2015, Pierre, A
      03/01/2015, Pierre, B
      04/01/2015, Pierre, C
      05/01/2015, Pierre, C
      06/01/2015, Pierre, A
      01/01/2015, Rachid, A
      02/01/2015, Rachid, D
      03/01/2015, Rachid, D
      04/01/2015, Rachid, D
      05/01/2015, Rachid, E
      06/01/2015, Rachid, E ];

       

      With this script, what is the best way to create an 'EventGroup' field. 'EventGroup' must change (increment) if 'Event' or 'Employee' has changed. Table1 is sorted by Date, Employee, Event.

       

      The final table should look like this :

       

      Date Employee EventEventGroup
      01/01/2015 Pierre A1
      02/01/2015 Pierre A1
      03/01/2015 Pierre B2
      04/01/2015 Pierre C3
      05/01/2015 Pierre C3
      06/01/2015 Pierre A4
      01/01/2015 Rachid A5
      02/01/2015 Rachid D6
      03/01/2015 Rachid D6
      04/01/2015 Rachid D6
      05/01/2015 Rachid E7
      06/01/2015 Rachid E7

       

      Thanks,

      Xavier.

        • Re: increment field on dimension changes
          Gysbert Wassenaar

          Maybe like this:

           

          Table1:

          LOAD *, if(Previous(Employee&'|'&Event) = Employee&'|'&Event,peek(EventGroup),1+peek(EventGroup)) as EventGroup Inline [
          Date, Employee, Event
          01/01/2015, Pierre, A
          02/01/2015, Pierre, A
          03/01/2015, Pierre, B
          04/01/2015, Pierre, C
          05/01/2015, Pierre, C
          06/01/2015, Pierre, A
          01/01/2015, Rachid, A
          02/01/2015, Rachid, D
          03/01/2015, Rachid, D
          04/01/2015, Rachid, D
          05/01/2015, Rachid, E
          06/01/2015, Rachid, E ];