topic Re: How does Autonumber function work? in QlikView

How does Autonumber function work?

Mon, 05 Jan 2015 14:00:49 GMT

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?

Re: How does Autonumber function work?

marcus_malinow — Mon, 05 Jan 2015 14:05:21 GMT

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.

Re: How does Autonumber function work?

rubenmarin — Mon, 05 Jan 2015 14:09:53 GMT

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!

Re: How does Autonumber function work?

Mon, 05 Jan 2015 14:14:01 GMT

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?

Re: How does Autonumber function work?

marcus_malinow — Mon, 05 Jan 2015 14:18:27 GMT

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

Re: How does Autonumber function work?

marcus_malinow — Mon, 05 Jan 2015 14:31:36 GMT

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