7 Replies Latest reply: Nov 11, 2017 2:05 PM by Marco Wedel RSS

    Autonumber Difference

    Pradeep S

      What is the difference between 2 expressions?

       

      Autonumber(Field1,Field2,Field3) as Key

      Autonumber(Field1&Field2&Field3) as Key

        • Re: Autonumber Difference
          Or Shoham

          I don't think Autonumber(Field1,Field2,Field3) is a valid QlikView expression. Autonumber() only takes two parameters.

           

          When concatenating values for keys, keep in mind that direct concatenation can sometimes produce unexpected results:

           

          Field 1 = 10

          Field 2 = 10

          Field 3 = 10

          Autonumber(101010)

           

          Field 1 = 1010

          Field 2 = 1

          Field 3 = 0

          Autonumber(101010)

           

          If this is possible in your data, consider separating your fields with something that's not going to appear in the data, e.g:

          Autonumber(Field1 & '-' & Field2 & '-' & Field3)

          Autonumber(10-10-10) or Autonumber(1010-1-0)

            • Re: Autonumber Difference
              Colin Albert

              It is also a good idea to name each range you are using for autonumber.

               

              Autonumber(Field1 & '-' & Field2 & '-' & Field3, 'ID1') as Key1

              Autonumber(Field4& '-' & Field5, 'ID2')     as Key2


              So Key1 and Key2 have their own ranges for autonumber.




                • Re: Autonumber Difference
                  Pradeep S

                  LOAD Name,

                       ID,

                       Marks,

                       total,

                       //autonumber(ID,Marks)as newfield,

                        autonumber(Marks& '-' &total,5)as newfield1,

                       Address

                  FROM

                  [State data.xlsx]

                  (ooxml, embedded labels, table is Sheet1);

                   

                   

                  The corresponding output :

                        

                  Address ID Marks Name newfield1 total

                  Bangalore 1 50 arjun rathinam 1 100

                  Bangalore 2 60 arvindh 2 100

                  Bangalore 2 20 arvindh 9 100

                  Bangalore 3 70 hari R 3 100

                  Bangalore 4 80 roshan 4 100

                  Bangalore 5 30 Pradeep 5 100

                  Bangalore 6 90 bala 6 100

                  Chennai 7 Mohan 7 100

                  Chennai 8 vignesh 7 100

                  Chennai 9 25 vinayak 8 100

                   

                  I am getting unique values but what is the use of '-'  and ID1 in Autonumber(Field1 & '-' & Field2 & '-' & Field3, 'ID1') as Key1?

              • Re: Autonumber Difference
                Marco Wedel

                Instead of concatenating Field1, Field2 and Field3 to get one single Autonumber() parameter you could as well use

                autonumberhash128() like this:

                 

                AutoNumberHash128(Field1, Field2 and Field3)
                

                 

                or for different counter instances without the need for value concatenating and separators maybe something like:

                 

                Autonumber(Hash128(Field1, Field2, Field3),'Counter1')
                

                 

                 

                hope this helps

                 

                regards

                 

                Marco