topic Re: Autonumber Difference in QlikView

Autonumber Difference

pradeep92 — Thu, 09 Nov 2017 11:43:34 GMT

What is the difference between 2 expressions?

Autonumber(Field1,Field2,Field3) as Key

Autonumber(Field1&Field2&Field3) as Key

Re: Autonumber Difference

Or — Thu, 09 Nov 2017 11:47:53 GMT

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 — Thu, 09 Nov 2017 12:56:58 GMT

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

pradeep92 — Thu, 09 Nov 2017 13:34:19 GMT

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

Colin-Albert — Thu, 09 Nov 2017 13:40:51 GMT

'-' 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.

Re: Autonumber Difference

Or — Thu, 09 Nov 2017 13:40:53 GMT

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

PrashantSangle — Thu, 09 Nov 2017 13:42:01 GMT

Hi,

check below thread

http://help.qlik.com/en-US/qlikview/12.1/Subsystems/Client/Content/Scripting/CounterFunctions/autonumber.htm

Regards,

Re: Autonumber Difference

MarcoWedel — Sat, 11 Nov 2017 19:05:26 GMT

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