How does Autonumber function work?

[#1]

Hello,

Can anyone tell me how the Autonumber function works correctly when applied to two different tables?

In QlikView Developer training I attended there was:

Order table

OrderID     LineNo

1               1

1               2

1               3

2               1

2               2

Order Details table

OrderID     LineNo

1               1

1               2

1               3

2               1

2               2

The autonumber function is used to generate a new key based on concatenation of OrderID + LineNo. Example Autonumber(OrderID & '-' & LineNo) as OrderLineKey.

How does QlikView ensure the numbers it assigns per combination is the same?

[marcus_malinow]

When QlikView assigns an Autonumber, it checks to see if the parameter has already been assigned an integer value. If so, it'll reuse the assigned number, otherwise it'll assign a new number.

Note that QlikView will only be able to work out whether the key has been used in the context of the current load script, so this is not a valid way of assigning a key in an incremental load script. If you were to try this you'd end up with clashing keys.

[rubenmarin]

And for a deeper explanation of how hash values works you can follow this links:

QlikView Addict: QlikView Functions: autonumber()

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

[#4]

Hi Marcus,

So you mean it checks is Order ID + LineNo combo of 1-1 has already been assigned a number? Does it check that against the first table numbers and then the second table. E.g. Will it check in Order Details table for the same combination?

[marcus_malinow]

Hi Michael,

yes, it'll check whether the key (Order Id + LineNo combo) has already been assigned a value.

I'm not too sure of the exact method it uses, I believe a temporary table is used for this.

Marcus

[marcus_malinow]

Michael,

sorry,

in answer to the second part of your question - yes it'll check that the combination has been used in any table.

So for example you could end up with something like

Order table

Order Id, Line No, OrderLineKey

1, 1, 1

1, 2, 2

2, 1, 3

Order Details Table

Order Id, Line No, OrderLineKey

1, 1, 1

1, 2, 2

2, 1, 3