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 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 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
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 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