topic AutoNumber() vs Auto..Hash() in QlikView

AutoNumber() vs Auto..Hash()

atsushi_saijo — Fri, 19 Dec 2014 18:09:30 GMT

Would you possibly help below clarifications:

The role of the counter (or AutoID) in AutoNumber((fieldname), (counter))

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?

The AutonumberHash128() - or 256

May I understand that AutoNumberHash128() generates arbitrary numbers based on the values instead of the load orders?

If so, the AutoNumberHash128() would be more superiour as our choice, since there is no chance of mixing-up counters. Particularly after data correction is made from the original OLTP data source (which already have primary keys), is there any advantage of using AutoNumber, risking potential mixed-up of the counter/AutoID?

I would appreciate for your advices.

Re: AutoNumber() vs Auto..Hash()

maxgro — Fri, 19 Dec 2014 18:26:34 GMT

here an example of counter usage in autonumber

http://community.qlik.com/message/464436#464436

Re: AutoNumber() vs Auto..Hash()

maxgro — Fri, 19 Dec 2014 18:27:36 GMT

and here an interesting post on hash in qlik

QlikView hash functions and collisions - The Qlik Fix! The Qlik Fix!

Re: AutoNumber() vs Auto..Hash()

rwunderlich — Fri, 19 Dec 2014 18:56:24 GMT

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

Re: AutoNumber() vs Auto..Hash()

atsushi_saijo — Mon, 22 Dec 2014 13:34:24 GMT

Dear Massimo, Thank you for the fast answer. I appreciated the two entries, and greatly improved my knowledge.

Re: AutoNumber() vs Auto..Hash()

atsushi_saijo — Mon, 22 Dec 2014 13:36:34 GMT

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.

Re: AutoNumber() vs Auto..Hash()

Clever_Anjos — Tue, 03 Oct 2017 14:14:15 GMT

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