Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

pradeep92
Contributor

Autonumber Difference

What is the difference between 2 expressions?

Autonumber(Field1,Field2,Field3) as Key

Autonumber(Field1&Field2&Field3) as Key

7 Replies
Or
Valued Contributor II

Re: Autonumber Difference

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

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.




pradeep92
Contributor

Re: Autonumber Difference

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

'-' is a separator between the fields so as Or explained in his reply yoyu can identify the difference between appending the three fields 10,10,10  or 101, 0, 10

Without a separator these would both return the same result when concatenated 101010 and give the same autonumber result.

With the separator these three fields would concatenate to 10-10-10 and 101-0-10 and autonumber will return two different results.

Or
Valued Contributor II

Re: Autonumber Difference

As explained in my original example, the concatenation is intended to avoid unintended duplication of keys.

Assigning an ID to each AutoNumber() is necessary when working with multiple AutoNumber() functions (and does no harm when working with a single one). It tells QlikView that each of these is a distinct Autonumber and should have its own range of numbers.

Re: Autonumber Difference

Re: Autonumber Difference

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

Community Browser