Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Would you possibly help below clarifications:
Further reading through google, it seems QlikView creates a counter during loading. When multiple autonumber is used for dimension tables, it seems we ought to put AutoID/Counter as unique ID for entire loading script. Is this correct understanding? But I wonder, is there any case where we really use the different AutoID/Counter? I do not understand why AutoId/Counter is needed. Would you please explain?
May I understand that AutoNumberHash128() generates arbitrary numbers based on the values instead of the load orders?
I would appreciate for your advices.
AutoNumber() generates sequential integers, which is a very storage efficient data for QV. When autonumbering different key fields in the same script, you should use the AutoId parameter. This ensures that each field will have sequential numbers with no gaps. It's a common practice to use the fieldname as the AutoId. eg
AutoNumber(OrderID, '%OrderKey") as [%OrderKey]
AutoNumber(InvoiceID, '%InvoiceKey") as [%InvoiceKey]
Another use case for the AutoId parm is when you are generating sequence numbers for a set. For example:
Ref Amount
A 100
A 150
B 75
Autonumber(recno(), Ref) as Sequence
Ref Amount Sequence
A 100 1
A 150 2
B 75 1
The Hash* functions generate a long string derived from the input. Hash functions are deterministic, that is, given the same input, they will always produced the same output. AutoNumber() is non-deterministic.Output values are dependent on the load order of input. For that reason, Autonumbering of keys should only be done in the final qvw. Autonumbered keys should not be stored in QVDs or intermediate qvws.
The AutoNumberHash*() functions combine hashing followed by autonumbering the hash value. Frankly, I've never found a reason to use the AutoNumberHash() functions. I use either AutoNumber() or Hash(), I can't think of a reason to combine them.
-Rob
here an example of counter usage in autonumber
and here an interesting post on hash in qlik
QlikView hash functions and collisions - The Qlik Fix! The Qlik Fix!
AutoNumber() generates sequential integers, which is a very storage efficient data for QV. When autonumbering different key fields in the same script, you should use the AutoId parameter. This ensures that each field will have sequential numbers with no gaps. It's a common practice to use the fieldname as the AutoId. eg
AutoNumber(OrderID, '%OrderKey") as [%OrderKey]
AutoNumber(InvoiceID, '%InvoiceKey") as [%InvoiceKey]
Another use case for the AutoId parm is when you are generating sequence numbers for a set. For example:
Ref Amount
A 100
A 150
B 75
Autonumber(recno(), Ref) as Sequence
Ref Amount Sequence
A 100 1
A 150 2
B 75 1
The Hash* functions generate a long string derived from the input. Hash functions are deterministic, that is, given the same input, they will always produced the same output. AutoNumber() is non-deterministic.Output values are dependent on the load order of input. For that reason, Autonumbering of keys should only be done in the final qvw. Autonumbered keys should not be stored in QVDs or intermediate qvws.
The AutoNumberHash*() functions combine hashing followed by autonumbering the hash value. Frankly, I've never found a reason to use the AutoNumberHash() functions. I use either AutoNumber() or Hash(), I can't think of a reason to combine them.
-Rob
Dear Massimo, Thank you for the fast answer. I appreciated the two entries, and greatly improved my knowledge.
Dear Rob, I appreciate for the in-depth feedback. It clarified the usage. I also note that I am the ardent reader of your blog. It is such a pleasant surprise to receive from such a guru in QlikView.
rwunderlich, there´s one situation when autonumberhash functions are more efficient
If you have to use a composite key, as autonumberhash functions use a comma separated parameter list they perform better than concatenating using "&"
I´ve made some tests (more explained here)
for short
Autonumberhash256(ID,Cod1,AnotherKey) perform much better than
Autonumber(ID&Cod1&AnotherKey)
of course you can write
Autonumber(hash256(ID,Cod1,AnotherKey)) but i think this could be slightly (not tested) slower