topic Re: What is the difference between Autonumber and Autonumberhash? in QlikView

What is the difference between Autonumber and Autonumberhash?

jblomqvist — Wed, 22 Apr 2015 14:19:32 GMT

Hello,

Can anybody please tell me what is the difference between Autonumber and Autonumberhash in QlikView?

I understand much better with a example so if anyone can show the difference it would be great please!

Re: What is the difference between Autonumber and Autonumberhash?

rwunderlich — Wed, 22 Apr 2015 17:42:55 GMT

AutoNumber() is a translate function that takes in a value and returns an integer that is than "mapped " to that value. The mapping is dependent on the order in which AutoNumber() is called.

AutoNumber('A') = 1

AutoNumber('X') = 2

AutoNumber('A') = 1

The Hash* functions take in value, process it through an algorithm and returns a "hash" representation of the input value. For a given input value, Hash*() will always return the same string.

hash128('abc') = M5(:B5)O1QEOW^6XQX9%7'

AutoNumberHash*() first hashes the string, then autonumbers it. It is the same as:

AutoNumber(Hash128('abc'))

The AutoNumberHash() functions in QlikView were a mistake (in my opinion). There is no practical need for them and they can be safely ignored.

-Rob

http://masterssummit.com

http://robwunderlich.com

Re: What is the difference between Autonumber and Autonumberhash?

jblomqvist — Thu, 23 Apr 2015 10:08:45 GMT

Hi Rob,

Thank you for your reply. Could you please explain what is meant by "hash"? I have heard of the terminology but never really understood it.

Also, what does the output mean after the "=" in your example: hash128('abc') = M5(:B5)O1QEOW^6XQX9%7'

? What is that output in human terms

Re: What is the difference between Autonumber and Autonumberhash?

rwunderlich — Thu, 23 Apr 2015 13:49:37 GMT

Wikipedia explains the general idea of hash function better than I can.

Hash function - Wikipedia, the free encyclopedia

In my example:

hash128('abc') = 'M5(:B5)O1QEOW^6XQX9%7'

means that if you invoke the hash128 function with the parameter 'abc', it will return the string 'M5(:B5)O1QEOW^6XQX9%7'. So if you used it in load statement like

hash128('abc') as X

X would contain the value 'M5(:B5)O1QEOW^6XQX9%7'..

Are you considering a particular application of these functions or trying to understand some already written code?

-Rob

Re: What is the difference between Autonumber and Autonumberhash?

jblomqvist — Mon, 20 Jul 2015 10:41:30 GMT

Hi Rob/all,

Sorry to comeback late to this question I am still trying to understand it further.

Why does hash128('abc') return 'M5(:B5)O1QEOW^6XQX9%7'? Why does it select that as the output?

I am just trying to understand it better so hope it's OK

Re: What is the difference between Autonumber and Autonumberhash?

avinashelite — Mon, 20 Jul 2015 10:45:06 GMT

autonumber(expression[ , AutoID])

Returns a unique integer value for each distinct evaluated value of expression encountered during the script execution. This function can be used e.g. for creating a compact memory representation of a complex key.

In order to create multiple counter instances if the autonumber function is used on different keys within the script, an optional parameter AutoID can be used for naming each counter.

Examples:

autonumber( Region&Year&Month )

autonumber( Region&Year&Month, 'Ctr1' )

autonumberhash128(expression {, expression})

Calculates a 128-bit hash of the combined input expression values and the returns a unique integer value for each distinct hash value encountered during the script execution. This function can be used e.g. for creating a compact memory representation of a complex key.

Example:

autonumberhash128 ( Region, Year, Month )

Re: What is the difference between Autonumber and Autonumberhash?

manojkulkarni — Mon, 20 Jul 2015 11:01:41 GMT

Hi Rob,

Is it good option to use AutoNumberHash(..) in generating unique key in our application. Key will be built wit combination of columns.

Re: What is the difference between Autonumber and Autonumberhash?

swuehl — Mon, 20 Jul 2015 11:15:14 GMT

Why does hash128('abc') return 'M5(:B5)O1QEOW^6XQX9%7'? Why does it select that as the output?

That's the result of some quite complex maths.

The hash function is a mapping function that allows to map arbitrary input values into a fixed value range (128 bit in that case), without revealing any insight to the input value, i.e. the returned string is not meaningful (it should look like a randomly selected value from a 128 bit text value range) and you won't and should not be able to trace back from that string to 'abc'.

Re: What is the difference between Autonumber and Autonumberhash?

rwunderlich — Mon, 20 Jul 2015 22:18:21 GMT

Yes, AutoNumberHash() is a reasonable way to create unique keys from a combination of columns. As long as the keys are used in the same QVW. They may not be stored in QVDs.

-Rob

Re: What is the difference between Autonumber and Autonumberhash?

DavidFoster1 — Fri, 16 Oct 2015 13:35:15 GMT

Hi Rob

What would you recommend for creating a surrogate key for a dimension with a compound primary key. I thought that AutoNumberHash256 was a good approach for creating a surrogate key, but your comment here says not.

Does this mean that the AutoNumberHash256 function would return different integers for the same value in different datasets depending on how many unique hashes there were?

Re: What is the difference between Autonumber and Autonumberhash?

jonathandienst — Fri, 16 Oct 2015 13:40:29 GMT

All the Autonumbering functions guarantee the correctness of the numbers within a single load. A subsequent load may not produce the same values, so they cannot be used as a meaningful key in a QVD intended for subsequent reloading and combining with other data (eg an incremental load)

Re: What is the difference between Autonumber and Autonumberhash?

Anders_Eriksson — Fri, 16 Oct 2015 13:47:21 GMT

Just a little correction for Rob's first example, the second 'A' would get the same number as the first 'A'.

AutoNumber('A') = 1

AutoNumber('X') = 2

AutoNumber('A') = 1

AutoNumber produces the same output every time for the same input as long as you are in one QVW.