4 Replies Latest reply: Nov 28, 2017 8:55 PM by MONAJ RATHI RSS

    restart autonumber qlik sense

    Joanna Seldon


      Hi

       

      each CustomerID  has number of OrderID

       

      This order id can look like this x11533322

       

      I wish to autonumber these, but restart the autonumber per customer

       

      I have tried to use in the load

       

      AutoNumber( CustomerID & '|' & OrderID ) as ID

       

      AutoNumber( OrderID ) as ID

       

       

       

       

      I wish for the output to show as

       

       

      CustomerID - ID

       

      A                     1

      A                     2

      A                     3

      A                     4

      B                     1

      B                     2

      B                     3

       

       

       

      Please help

       

        • Re: restart autonumber qlik sense
          Antonio Mancini

          Hi Joanna,

          maybe

           

          Autonumber(RowNo(),CustomerID) as ID

           

          Regards,

          Antonio

            • Re: restart autonumber qlik sense
              Sunny Talwar

              Or even this:

               

              AutoNumber(RecNo(), CustomerID) as ID

               

              While I have heard that AutoNumber is sometime slow for huge databases, so you can also use Peek/Previous

               

              If(CustomerID = Previous(CustomerID), RangeSum(Peek('ID'), 1), 1) as ID

               

              But make sure to order your data if it is not already ordered by CustomerID

              • Re: restart autonumber qlik sense
                MONAJ RATHI

                Hi,

                I have similar issue but I need to autonumber on multiple columns, but the autonumber gives same output number if the value exists in other column.

                 

                In below, if you see for value "200" the autonumber is 3 and it is assigned wherever I have 200 across the columns. Let me know if I can use any other function or how I can reset the autonumber counter back to 1.

                 

                ID  a1  a1_Autonum n1  ni_autonum

                1   100  1         200  3

                2   200  3         150  9

                 

                base:

                load * Inline [

                id, a1, n1, r1

                1,100, 200, 300

                2,200, 150, 240

                3,250, 101, 750

                4,600, 150, 225

                5,1000, 845, 990

                6,101, 1234, 8954

                7,200, 345, 675

                8,600, 750, 101

                9,550, 350, 605

                10,500, 185, 230

                ];

                 

                let c = NoOfRows('base');

                 

                test1:

                load *,AutoNumber(a1) as a1rnk, $(c) as cnt

                resident base

                order by a1;

                 

                test:

                load *,'' as samplecolumn resident test1;

                 

                drop table test1;

                 

                test2:

                load AutoNumber(n1) as n1rnk, id, 'test2' as test2

                resident base

                order by n1;

                 

                left join (test)

                load n1rnk, id resident test2;

                 

                drop table test2;

                drop table base;

                 

                exit script;