Hello everyone and welcome to QlikFreak. Since this is our last entry of the year and many of you are enjoying your holidays away from the office, I thought it would be cool to try something a little different. Today, instead of having a tutorial or a fun Qlik app, I created a Qlik trivia with interesting scripting scenarios that will make you think twice before writing down your answers!
I know you don’t want me to spoil all the surprises, so we’ll check the answers at the end of the post. So anyways, without further ado, let the games begin! OK, no, wait, before you go, there’s one more thing… I think you’ll get the same results regardless of the version you’re using, but just in case, I’m working with QlikView 12 SR4 and Qlik Sense 3.1 SR4 😛
If you’re an experienced Qlik Developer, chances are that you’ve used DISTINCT in more than one of your scripts, right? Well then, tell me how many records will this table have:
a) 4 records b) 3 records c) 0 records d) OMG, he’s using Qlik Sense
Tip: The last two records are exactly the same.
Mapping tables are one of my favorite things in Qlik. They’re small, efficient and very easy to use. If you’re not familiar with them, I’d strongly recommend you to read this post by Aaron Couron.
Our question is: How many records will have the correct name in the “Country” field?
a) 0 records b) 2 records c) 4 records d) How can Aaron write so many posts?
Tip: QlikView auto-concatenates all the tables that share the same structure. But what happens with mapping tables?
Here’s an easy one: In TABLE_2, what happens first, the “ORDER BY” clause or the “peek” calculation?
a) ORDER BY b) peek c) At the same time d) Is dinner ready?
Tip: I’ll have lasagna for dinner. My mom cooks the best lasagna in the country.
Here’s an XML file with 9 fields. However, I don’t want to write them one by one, so I use * to load everything. What’s going to happen when I reload?
a) Error b) Warning c) Successful reload d) Damn, I’m hungry
Tip: I miss the green comments… I know that’s not a tip, but I miss them.
Speaking about XMLs, what would happen if I add fields that don’t exist in the file in this load statement?
a) Error b) Warning c) Successful reload d) Ehhh Macarena
Tip: Those are not calculations for new fields, they’re just fake field names.
This one is for hardcore Qlikers. All of these scenarios do exactly the same. They build a composite key by concatenating multiple fields and then, they perform an applymap based on that key. Which of these chunks of code will run faster?
a) Scenario 1 b) Scenario 2 c) Scenario 3 d) Why am I reading this?
Tip: Scenario 1 is performing the same calculation twice in the same load statement. Scenario 2 is more elegant as it uses a preceding load. Scenario 3 doesn’t give a damn and creates a temp table.
Ready? Pencils down!
Well, those were our questions for today’s trivia. If you want to check them directly in QlikView, you can download the materials here. Are you ready for the answers? Here we go!
Answer 1: A (4 records)
Yep, the preceding load does something funny with our DISTINCT clause. If you want to get only 3 records, you should cut it and paste it in the upper load!
Answer 2: B (2 records)
This one’s tricky. If you name both tables, QlikView will only use the first mapping table and fill 2 records. Strangely, if you comment the name in the second one, both tables get concatenated and all the records are filled. True story.
Answer 3: A (ORDER BY)
You order first and theeeeen… perform the calculations.
Answer 4: A (Error)
I’m not sure why this happens, but you can’t use * in XMLs! (Sorry for the messages in Spanish, you know how picky Sense Desktop is… I can’t change the language… And I don’t like to develop in my browser)
Answer 5: C (Successful Reload)
These files are really weird. Normally, if you add a non-existent field, you’ll get a classic “Field not found” error. However, XML files just don’t care about your rules and create an empty field.
Answer 6: C (Scenario 3)
I’ll be honest here. During my first years as a Qlik consultant I always recommended using preceding loads. I thought they were elegant and efficient. And well, after a couple of years I learnt that those attributes are not always there. When you’re working with certain data sets (especially large ones), preceding loads can increase your reload times A LOT.
In this example, Scenario 2 (the one with the preceding load) takes more or less 35 seconds. Despite performing the string concatenations twice in the same LOAD statement, Scenario 1 runs in 20 seconds (a lot faster than the preceding load). Finally (and surprisingly), Scenario 3, which creates a temp table (using more memory at that moment), runs in… wait for it…. 15 seconds! I’m not saying this will always be the best way to tackle your performance issues, but I’d recommend you to try several approaches before deploying your app.
If you want to learn more about the cost of preceding loads, be sure to check Rob Wunderlich’s post here!
Did you like the trivia? How many right answers did you get?
Hey, before you go I have 2 more things to share. First, don’t forget that every December, PACKT Publishing has an insane sale and gives away all their eBooks and videos for $5 USD! Nothing says “I love you” like a copy of Creating Stunning Dashboards with QlikView, QlikView Unlocked or Mastering QlikView Data Visualization, right?
And last but not least, here’s QlikFreak’s 2016 Christmas Card! This year, I chose to visualize Christmas carols using QlikView 😀
I hope you enjoyed today’s post. If you have any questions, comments or suggestions, don’t hesitate to use the section below. Thanks for reading, enjoy your holidays and keep qliking!