21 september 2022 Qlik Autonumber pitfalls Deel dit bericht 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? More from the Bitmetric team Qlik Cloud Backup Protect your investment in Qlik with daily incremental backups stored in an encrypted environment with redundant storage. Available for as little as 2 Euro per day. Learn more. Join the team! Do you want to work within a highly-skilled, informal team where craftsmanship, ingenuity, knowledge sharing and personal development are valued and encouraged? Check out our job openings. Friday Qlik Test Prep Script Solution Hoe kunnen we je ondersteunen? Barry beschikt over meer dan 20 jaar ervaring als architect, developer, trainer en auteur op het gebied van Data & Analytics. Hij is bereid om je te helpen met al je vragen. Bel ons Mail ons 16 april 2025 The Cost of Bad Data: What Is It Really Doing to Your Business? Inaccurate or outdated data doesn’t just cause small hiccups. This can severely impact your bottom line. It slows down your teams, leads to expensive errors, and creates serious compliance risks. The good news is that these challenges are avoidable. TimeXtender 3 april 2025 Rethinking Pop-Ups in Qlik Cloud: A Simple Yet Effective Solution Struggling with the lack of native pop up support in Qlik Cloud? Learn how we created a smooth and user friendly alternative using guided sheet transitions with only native Qlik functionality. No extensions or layout issues involved. Qlik Solution Visualization 10 maart 2025 Qlik Data Flow: Simplifying Data Transformation Without Code Qlik Data Flow simplifies data transformation with a visual editor. Clean, join, and reshape data using drag and drop, no coding needed. Automatically generate Qlik script as you build. Learn how it works, see a step-by-step example, and compare it to Qlik Data Manager and Qlik Script. ETL No-Code Qlik Script
16 april 2025 The Cost of Bad Data: What Is It Really Doing to Your Business? Inaccurate or outdated data doesn’t just cause small hiccups. This can severely impact your bottom line. It slows down your teams, leads to expensive errors, and creates serious compliance risks. The good news is that these challenges are avoidable. TimeXtender
3 april 2025 Rethinking Pop-Ups in Qlik Cloud: A Simple Yet Effective Solution Struggling with the lack of native pop up support in Qlik Cloud? Learn how we created a smooth and user friendly alternative using guided sheet transitions with only native Qlik functionality. No extensions or layout issues involved. Qlik Solution Visualization
10 maart 2025 Qlik Data Flow: Simplifying Data Transformation Without Code Qlik Data Flow simplifies data transformation with a visual editor. Clean, join, and reshape data using drag and drop, no coding needed. Automatically generate Qlik script as you build. Learn how it works, see a step-by-step example, and compare it to Qlik Data Manager and Qlik Script. ETL No-Code Qlik Script