Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
What is the difference between 2 expressions?
Autonumber(Field1,Field2,Field3) as Key
Autonumber(Field1&Field2&Field3) as Key
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)
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.
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?
'-' 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.
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.
Hi,
check below thread
Regards,
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