topic Re: Does Autonumber work with SQL Union Statement? in QlikView

Does Autonumber work with SQL Union Statement?

jrdunson — Mon, 26 Jan 2026 18:19:17 GMT

I'm trying to use the Autonumber() function to remove the Symbol width... for performance tuning.

However it's not working...

Background, we are adding tables together via an SQL union

1. The first table uses 'gkey' as primary key (about 2,000,000 rows)

2 The secondary table uses 'dkey' as 'gkey' as the primary key [but we add 9 Trillion to the dkey, to avoid any overlapping, duplicate keys: so 561,930 becomes 9,000,000,561,930...so after the union, all the keys are still distinct, howbeit there is a large gap between keys from different tables]

My question is... can I use autonumber with an SQL Union statement? Could the SQL union be causing the problem?

Here is my Load statement, and SQL Union statement....

Table1:

LOAD

Autonumber(gkey,'gkey') AS [Primary Gkey],

[other fields],

...

;

SQL

Select

gkey,

[other fields],

...etc.

UNION

Select

dkey as gkey.

[other fields],

...etc.

;

Here is my current size (in Rob Wunderlich's Document Analyzer):

Distinct Values Symbol Width ~ "Symbol Bytes"

2,214,889 8.00 19,934,001

Re: Does Autonumber work with SQL Union Statement?

Clever_Anjos — Wed, 09 Sep 2015 20:23:06 GMT

Yes you can, autonumber and union work in differente levels (QlikView x SQL )

Re: Does Autonumber work with SQL Union Statement?

maxgro — Wed, 09 Sep 2015 20:34:07 GMT

However it's not working...

could you explain the problem?

syntax error? wrong result?

Re: Does Autonumber work with SQL Union Statement?

jrdunson — Wed, 09 Sep 2015 20:34:32 GMT

Thanks Clever,

However, I'm not sure, why, after I use autonumber doesn't reduce my Symbol Width to ) (.e.g. Sequential numbers occupy zero Symbol storage... the state table used as proxy). Shouldn't my Symbol width be 0 ?

Re: Does Autonumber work with SQL Union Statement?

jrdunson — Wed, 09 Sep 2015 20:36:10 GMT

Sequential numbers should occupy zero Symbol storage... I should get this:

Distinct Values Symbol Width "Symbol Bytes"

2,214,889 0 2,214,889

Not this...

Distinct Values Symbol Width "Symbol Bytes"

2,214,889 8.00 19,934,001

Re: Does Autonumber work with SQL Union Statement?

Clever_Anjos — Thu, 10 Sep 2015 16:47:11 GMT

I´m sure about sequential numbers requiring 0 bytes,

Symbol Tables and Bit-Stuffed Pointers

Re: Does Autonumber work with SQL Union Statement?

Clever_Anjos — Thu, 10 Sep 2015 16:50:44 GMT

I think you´ll need 22 bits to represent up to 4.194.304 different values

21 bits would store only 2.097.152 different values