topic Re: Autonumber function: How does QlikView know which value to assign when function is used in two tables to form a relationship? in QlikView

Autonumber function: How does QlikView know which value to assign when function is used in two tables to form a relationship?

jblomqvist — Fri, 01 May 2015 10:52:37 GMT

Hello,

Can anyone tell me how QlikView knows which number to assign when using Autonumber function in two tables?

E.g:

TableA

Key1	Key2	AutoNumber
1	1	1
1	2	2
2	1	3
3	1	4

TableB:

Key1	Key2	Autonumber
1	1	1
1	2	2
3	1	4
4	1

Is it correct that in Table B for Key 3-1 it would assign a 4 and not the value of 3?

I don't understand if QlikView would do this check or how it does this check when using the autonumber function. Can anyone explain please?

Re: Autonumber function: How does QlikView know which value to assign when function is used in two tables to form a relationship?

Gabriel — Fri, 01 May 2015 11:28:13 GMT

Hi,

Autonumber is not the best to use to create association in qlikview. Hashxx() function and relatives are good to create association.

Hashxxx() functions are deterministic. If you're thinking of storing data into QVD files then this is good to use.

AutoNumberHashxxx() functions combine hashing followed by autonumbering the hash value.

Re: Autonumber function: How does QlikView know which value to assign when function is used in two tables to form a relationship?

maxgro — Fri, 01 May 2015 11:37:16 GMT

from help

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.

Is it correct that in Table B for Key 3-1 it would assign a 4 and not the value of 3?

yes, as it was in T1

script

T1:

LOAD Key1 as Key11,

Key2 as Key21,

AutoNumber(Key1 & '-' & Key2) as AutoNumber

FROM

[https://community.qlik.com/thread/162432]

(html, codepage is 1252, embedded labels, table is @1);

T2:

LOAD Key1 as Key12,

Key2 as Key22,

AutoNumber(Key1 & '-' & Key2) as AutoNumber2

FROM

[https://community.qlik.com/thread/162432]

(html, codepage is 1252, embedded labels, table is @2);

result

Re: Autonumber function: How does QlikView know which value to assign when function is used in two tables to form a relationship?

MarcoWedel — Fri, 01 May 2015 14:15:11 GMT

Hi,

like already said, one way to create a combined key out of keys 1 and 2 could be also:

LOAD somefields,
          AutonumberHash128(Key1, Key2) as %CombinedKey
FROM yoursource;

hope this helps

regards

Marco

Re: Autonumber function: How does QlikView know which value to assign when function is used in two tables to form a relationship?

jblomqvist — Fri, 01 May 2015 15:16:29 GMT

Hello,

Can someone please describe what the Autonumberhash function do? What does the hash mean?

Sorry I can't get my head around it!

Re: Autonumber function: How does QlikView know which value to assign when function is used in two tables to form a relationship?

MarcoWedel — Fri, 01 May 2015 16:01:29 GMT

see help:

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 )

regards

Marco