What is the difference between Autonumber and Autonumberhash?

[jblomqvist]

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!

[rwunderlich]

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

[jblomqvist]

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

[rwunderlich]

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

[jblomqvist]

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

[avinashelite]

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 )

[manojkulkarni]

Hi Rob,

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

[swuehl]

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

[rwunderlich]

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

[DavidFoster1]

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?