Mastering Qlik Sense

In ancient times, there were not a lot of options to learn QlikView. The easiest one was to enroll in one of QlikTech’s official courses. However, they were not as frequent (nor as cheap) as you’d like. Most of the Qlik-dinosaurs… I mean, the most experienced consultants I know, learned this way. The less fortunate (i.e. trainees like me), were forced to follow the exciting and not-at-all depressing path of reading the documentation files. Yeah, those massive PDFs that got installed alongside QlikView with 2,750 pages full of fun! Sounds useful, doesn’t it?

Luckily, those days are far over. Today, you can choose from a wide variety of learning options. The official Qlik courses are still there, but now they are taught much more often and in different languages and cities all around the globe. Some partners (like us), have even developed our own content to give customers a better experience with more examples and real-world problems.

Do you hate large groups of people? No problem, you can enroll to Shilpan’s course in Udemy or spend some time navigating in the Qlik Continuous Classroom from your office or home. Are you already an expert? Well, extend your skills with advanced courses like the Masters Summit, Qlik Sense Deep Dive or Websy / Vizlib’s workshops. Even the official (and super boring) documentation has evolved into a friendly portal that you can access anytime!

Qlik-Trivia

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 😛

Question 1:

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.

Threshold Bar Chart + Christmas Card

One of the biggest problems about being a QlikView Developer is that every time you see an interesting visualization, you immediately start wondering how you can use it in your own dashboards. For instance, a couple of days ago, I was wandering around in Twitter when I saw a message which, apparently, had something to do with the quality of the air in Berlin (sorry, but my German is not very good).

As you can see, the structure of this chart is not overly complex: classic bar chart using a time dimension and a standard expression. However, a simple visual cue composed by a reference line and a different color makes it easier to identify where (of in this case, when) an element surpasses the threshold. In my opinion, it is an elegant and useful way to present data.

Since I didn’t have access to this particular data set, I decided to work with a sales QVD from one of the projects I’m currently working on and, after spending a few minutes in QlikView, this was the result:

I don’t know if this kind of representation has an official name, but I called it “Threshold Bar Chart” <<Patent pending>>. I know… I’m awful at naming things so, if you have a better title, be sure to share it in the comments section! As usual, you can download the QVW with the final chart here. The recipe is simple:

Know your reloads!

QVDs are –without any doubts– one of the best parts in QlikView. They are small, super-fast and allow us to use data more efficiently. Most of the developers out there already have some sort of template for loading information from multiple sources and storing them in QVDs, but last week I found that metadata about each reload, table and data source could be useful. Here’s my story.

I have a customer that has 3 main data sources: Oracle, SQL Server and some XLS files. As usual, I created a layered model and everything was working perfectly. However, our extraction phase was not very consistent. It was executed 4 times per day, but the reload times were erratic (from 4 to 50 minutes).

Normally, we can assume that the reloads are slower in certain moments of the day depending on the network traffic, the workload on the transactional systems or database maintenance routines. But this was different, our reloads had great discrepancies even if you compared the same hours and days.

As you know, QlikView Server sometimes gives you valuable information like the duration of the tasks and you can always activate the QVW log to find out more data about the last reload. But we needed something more, so I created a small routine that stored the tables in QVD files but also kept track of the execution of each element.

When you open the QVD Generator, you can find useful information like the tables that were loaded, the numbers of fields / rows and the reload time for each one of them. Also, as the routine stores the metadata in QVD files, you can create new applications to analyze the average time for each table individually and compare different schedules in order to choose the most efficient one.