30 November 2022 Preceding Loads in Qlik Sense 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 preceding loads: If we have to take value judgement on this question based on the amount of likes versus answers, we could maybe say that a preceding load is something people are very interested in, but not much is known about? In any case: The correct answer is B. This question actually consist of two parts. We have encountered a preceding load, but also the possibility to use either RowNo() or RecNo(). Lets dive in and find out why answer B is correct in this case. Preceding Loads With a preceding load it is possible to pass the previously loaded fields to a new load and do transformations on them while still loading this to the same output table. Please refer to the figure below: Here we can see that the Sales output table is loaded from Sales.qvd. By adding a new load statement on top of this one, we can pass fields from that first, initial load to a new load. By using the asterisk (*) we determine to carry over all fields, however it is also possible to make selections and only load specific fields. The second load is a new load on those fields, making it possible to do new transformations on those fields, illustrated by creating the measure #PriceExclDiscount, using SalesPrice and DiscountAmount. Remember this order, since this is an important part of answering the question. Preceding loads can be useful if you need to do multiple calculations or aggregations on the dataset. It will make for clearer and easier to understand script. The example above is quite small, but in some cases when many different prices have to be calculated from a single origin, it is often best to first do the basic calculations in the first load and then load all the measures in the second load statement. RowNo() vs RecNo() The RowNo() and RecNo() functions appear similar in function, assigning an integer to the row being loaded, however they have a distinct difference. Where RecNo() looks at the input table and assigns an integer to each row being loaded, RowNo() will look at the output table when assigning numbers to each row. Answering the question With our newfound understanding of preceding loads and RowNo() and RecNo() let’s head back to the question. Answer C can be scrapped immediately since removing the alias will not solve anything. For the other answers the load order comes into play. The bottom part of the load statement is loaded first after which the second load will follow. We have just learned that RowNo() will only evaluate the output table. Since the bottom (first) part of the preceding load is not the output table, RowNo() will not evaluate this and return 0. This means that removing the where exists clause is also not the answer, so D is out of the question. Then we are left with answers A or B. Changing RowNo() to RecNo() in the question will work, since RecNo() looks at the input table and assigns records to each row of the input table. However, we are looking for RowIDs and not RecordIDs. The difference? Since RecNo() looks at the input table, it doesn’t take the where exists clause in account, whereas RowNo() by looking at the output table does. To illustrate this let’s imagine that we have three ProductID’s previously loaded: A, B and D. And we load the following table using the where exists(ProductID) clause: Products C and E will be left out of the output table. However since RecNo() has started on the input table, the assigned numbers will differ from the RowNo() function. Therefor this is unusable as a RowID. Leaving us with answer B: move the RowNo() function from the bottom load to the top load. Some other things to remember You can stack as many preceding loads as you wish You can choose which fields to pass to the next load. Wildcard (*) being most commonly used to include all The granularity for each load can be different Wildcard filenames don’t work. You have to be specific about which file to load That’s it for this week! 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 Solution How can we help? Barry has over 20 years experience as a Data & Analytics architect, developer, trainer and author. He will gladly help you with any questions you may have. Call us Mail us 10 March 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 6 March 2025 Just because your data is in the cloud does not make it safe. Qlik Cloud does not automatically back up your data, leaving your apps vulnerable to loss, corruption, or accidental deletion. Learn why a backup solution is essential and how Bitmetric’s Qlik Cloud Backup keeps your data safe. Take action now to protect your analytics Governance Qlik Security Support 24 February 2025 Update: Qlik Cloud icons collection Discover our updated collection of 239 Qlik Cloud icons on GitHub. Featuring new Data Flow icons in SVG and PNG formats, perfect for enhancing your Qlik schematics and documentation. Qlik Visualization
10 March 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
6 March 2025 Just because your data is in the cloud does not make it safe. Qlik Cloud does not automatically back up your data, leaving your apps vulnerable to loss, corruption, or accidental deletion. Learn why a backup solution is essential and how Bitmetric’s Qlik Cloud Backup keeps your data safe. Take action now to protect your analytics Governance Qlik Security Support
24 February 2025 Update: Qlik Cloud icons collection Discover our updated collection of 239 Qlik Cloud icons on GitHub. Featuring new Data Flow icons in SVG and PNG formats, perfect for enhancing your Qlik schematics and documentation. Qlik Visualization