Qlik Autonumber pitfalls

Every Friday at Bitmetric we’re posting a new Qlik certification practice question to our LinkedIn company page. Last Friday we asked the following Qlik Data Architect certification practice question about pitfalls of the AutoNumber function in Qlik:

This was mainly a ‘careful reading’ question, which many of you did fortunately.

The correct answer is C: change Autonumber(LocationID, ‘Company’) to Autonumber(LocationID, ‘Location’)

The gotcha in this question is that the Autonumber() function is using a different ‘namespace’ for the same field. In the Company table the Company namespace is used for %Location_KEY, while in the Location table the Location namespace is used for this field.

Namespace?

The namespace is the list where Qlik stores the values that are autonumbered. By providing a different namespace, you create a separate list. Let’s see how this works by iterating through our example script. First, for the sake of clarity, we’ll make a small change to the script though. Rather than using sequential numbers for the ID’s, we’ll use a short text code. This way it’ll be easier to distinguish between original values and autonumbered values.

The revised script looks like this:

The main issue is in lines 43 to 48. Running the script using the same namespace (Company) for different source fields leads to a mixed list:

Then in lines 50 to 54, we’re suddenly autonumbering the LocationID field using a different namespace (Location). This creates a new list of sequential numbers:

The result is that both tables will share a key (%Location_KEY), but because this key was autonumbered using different namespaces, the values will not line up. For example, the BIT company is located in Aalsmeer, but is now suddenly associated with Utrecht.

How can we prevent Autonumber mistakes?

There are multiple ways to prevent mistakes like these from happening:

Doublecheck that you’re using the correct namespace for the field in the Autonumber() function

Changing the namespace from Company to Location corrects this mistake.

Don’t provide a namespace in the Autonumber() function

Alternatively, not providing a namespace will add all the values to a single namespace. This would result in the following list:

The downside of this approach is that individual fields are now no longer sequentially numbered, so it is not preferred to take this approach.

The Autonumber() function is not consistent across apps
The Autonumber() function numbers the data in the order that it gets loaded. If the order of the data changes, so does the assigned autonumber value. This is important to keep in mind. It is not recommended to use autonumber on the same field in different apps, as the values may not necessarily be the same.

A better alternative: the Autonumber script statement

A better approach to using the Autonumber() function is to use the Autonumber script statement. Using the script statement, we can autonumber multiple fields at the same time using a wildcard. Implementing this would result in the following script:

Notice that we no longer use the Autonumber() function on lines 46, 47 and 52. Instead, we’ve added a single statement on line 60 to autonumber every fields that starts with ‘%’ and ends with ‘_KEY’.

Not only is this operation much faster than the Autonumber() function. (because it operates on the symbol tables directly, a topic for another day) It’s also much more convenient during development. By commenting out the Autonumber statement we’ll see the original values in our data model, making it much easier to spot errors in associations.

That’s it for this week. See you next Friday?