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 Take your Qlik skills to the next level! Since 2013, the Masters Summit for Qlik is the premier advanced training for Qlik. Join us in Vienna and take your Qlik skills to the next level. Join the team! Enjoying these challenges? We have many more, and we’ll even pay you to solve them 😉 Check out our job opening. 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 25 April 2024 Over 3000 Qlik Sense servers still vulnerable to Cactus ransomware In November of last year we reported on how two critical vulnerabilities in Qlik Sense Enterprise for Windows were exploited in the Cactus ransomware campaign. As of today, over 3000 vulnerable Qlik Sense servers remain connected to the Internet. New Release Qlik Vulnerability 29 March 2024 Dealing with missing and late arriving values with OtherSymbol and other solutions Learn effective strategies for handling missing or late-arriving dimension values in Qlik with our expert guide. Discover how to use OtherSymbol, WHERE NOT EXISTS, and other solutions to deal with early-arriving facts Data Model Friday Qlik Test Prep Qlik Solution 22 March 2024 Picking the right color palette for your visualization Discover how to choose the perfect color palette for your data visualizations with our expert guide. Learn about single, categorical, sequential, and diverging color palettes to enhance your charts and graphs. Ideal for data analysts and visualizers. Friday Qlik Test Prep Qlik SenseTheme Visualization
25 April 2024 Over 3000 Qlik Sense servers still vulnerable to Cactus ransomware In November of last year we reported on how two critical vulnerabilities in Qlik Sense Enterprise for Windows were exploited in the Cactus ransomware campaign. As of today, over 3000 vulnerable Qlik Sense servers remain connected to the Internet. New Release Qlik Vulnerability
29 March 2024 Dealing with missing and late arriving values with OtherSymbol and other solutions Learn effective strategies for handling missing or late-arriving dimension values in Qlik with our expert guide. Discover how to use OtherSymbol, WHERE NOT EXISTS, and other solutions to deal with early-arriving facts Data Model Friday Qlik Test Prep Qlik Solution
22 March 2024 Picking the right color palette for your visualization Discover how to choose the perfect color palette for your data visualizations with our expert guide. Learn about single, categorical, sequential, and diverging color palettes to enhance your charts and graphs. Ideal for data analysts and visualizers. Friday Qlik Test Prep Qlik SenseTheme Visualization