21 September 2022 Qlik Autonumber pitfalls Share this message 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 appsThe 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? See you at QlikWorld 2023 in Vegas? Come see us at QlikWorld 2023 for a chance to win a free, lifetime SenseTheme subscription and get some cool swag. Want more Friday Qlik Test Prep? Check out the Friday Qlik Test Prep archive for more Qlik questions and answers. Friday Qlik Test Prep Script Solution How can we help? Feel free to contact us if you have any comments or questions. Call us Mail us 23 May 2023 What’s New in Qlik Sense May 2023 for Administrators This blog post provides Qlik Sense administrators a summary of the new administrative features and improvements available in Qlik Sense Enterprise on Windows. Let’s get started on what’s new in Qlik Sense for May 2023. New Release Qlik 23 May 2023 What’s New in Qlik Sense May 2023 for Business Users, Analytic Creators and Data Integrators This blog post provides Qlik Sense business users, analytic creators, and data integrators a summary of the features and improvements available in Qlik Sense Enterprise on Windows. Let’s get started with what’s new in Qlik Sense for May 2023. New Release Qlik 11 May 2023 How to make seasonal trendlines in Qlik Sense 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 how to make seasonal trendlines: The correct answer was B! Trend analysis Qlik has added Time series decomposition modifier functions to the line chart. This can […] Friday Qlik Test Prep Solution
23 May 2023 What’s New in Qlik Sense May 2023 for Administrators This blog post provides Qlik Sense administrators a summary of the new administrative features and improvements available in Qlik Sense Enterprise on Windows. Let’s get started on what’s new in Qlik Sense for May 2023. New Release Qlik
23 May 2023 What’s New in Qlik Sense May 2023 for Business Users, Analytic Creators and Data Integrators This blog post provides Qlik Sense business users, analytic creators, and data integrators a summary of the features and improvements available in Qlik Sense Enterprise on Windows. Let’s get started with what’s new in Qlik Sense for May 2023. New Release Qlik
11 May 2023 How to make seasonal trendlines in Qlik Sense 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 how to make seasonal trendlines: The correct answer was B! Trend analysis Qlik has added Time series decomposition modifier functions to the line chart. This can […] Friday Qlik Test Prep Solution