10 January 2022 Qlik optimized load statements Share this message For the new year, we’re doing something new at Bitmetric. Each Friday we’ll share a test question that is representative of what you’ll find on the Qlik Business Analyst, Data Architect or System Administrator certification exams. Including the strange or vague wording that’s sometimes found in these exams 😉 We’ll follow up each Monday with the correct answer, as well as some additional explanation and insights. We hope this will help many of you prepare for your Qlik certifications, or at the very least provide a bit of fun and discussion. Last Friday, we posted the following question about Qlik optimized load: The correct answer is answer C This provides an optimized load of the QVD which is the fastest way of loading a QVD into Qlik. If we look at the others options, we’ll see that answers A and B will also work but neither will have an optimized load (more on that below). Answer D does not even work at all because it creates a duplicate field in the table which leads to a script error (field names must be unique within a table). Even without the script error, the expression does not limit the loaded rows, it only sets null values for countries that aren’t ‘The Netherlands’. So why would you want an optimized load? For speed! 🚀 An optimized QVD load is the fastest way to load data from a QVD into Qlik. And while even a non-optimized load from is typically much faster than loading from other sources, the difference between an optimized load and non-optimized can be significant. For example, on a sample set of 22 million rows the optimized load was 3 times faster than a non-optimized load. Imagine the difference when you’re dealing with 100’s of millions of rows or if you need to load data from many different QVDs. This will save load time and will keep you from getting distracted while waiting for the reload dialog to finish 😉. Of course this also applies to server reload performance when you’re running scheduled tasks. How do you ensure an optimized load? Many operations will cause a QVD load to be non-optimized. To keep it optimized, limit your operations to: Renaming fields (using an alias). You can also load the same field twice under a different alias. This can be useful to create a separate key field. Omitting fields by not including them in the LOAD statement Use a single WHERE EXISTS, with a single parameter. So WHERE EXISTS([Country]) is OK, WHERE EXISTS([Country], [ISO Country Code]) is not. JOIN, KEEP or CONCATENATE with another table LOAD DISTINCT will also keep a load optimized. The DISTINCT part will be processed after the LOAD however, so you might still want to think twice before applying it to very large QVDs. The following operations prevent an optimized load. If you want an optimized load then don’t do any of the following: Transform a field. For example, Upper([Country]) AS [Country Capital]. Or by using an ApplyMap() Using a WHERE clause, other than a single WHERE EXISTS(). This is why answers A and B will not result in an optimized load. Load data into a mapping table Alias the field you’re using in the WHERE EXISTS clause How can you check if a load is optimized? Besides manually checking your script for the points listed above, the easiest way to check is to keep an eye on the script log, either in the data load progress window or the log file. If you see (QVD (row-based) optimized) then you’ll know you have an optimized load. Should you always use an optimized load? If it’s specifically asked on your Qlik certification exam then yes, definitely! In real life? It depends. Remember that 22 million row sample set with the impressive performance gains (3 times faster! 🚀) that we mentioned above? It went from 3 seconds to 1 second. In that scenario optimization is completely unnecessary. Our advise is to focus on creating readable and maintainable scripts first (have you seen our coding conventions?), and to worry about optimized loads (and performance in general) only when it’s expected to become an issue. Or as Donald Knuth succinctly put it: “Premature optimization is the root of all evil”. Want to maintain optimized load while concatenating tables? Check out our blog post about Qlik optimized load and concatenation. 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 Performance Script 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