Category Archives: Uncategorized

QlikView to Qlik Sense Convertor

Are you migrating QlikView Apps to Qlik Sense?  Have you tried the new QlikView Convertor tool in QS 3.2? 

The QV Convertor tool is available from the Dev Hub in QS 3.2+.  It’s a pretty slick tool that converts QV Variables, Dimensions and Expressions to Master items and converts QV charts to Master QS Visualizations.  It does not attempt to replicate the sheets,  but expects you to place the visualizations on sheets yourself as required.

It’s a very useful tool with a good UI that allows for filtering and limiting what gets converted.

At the Atlanta Qlik Dev Group meeting on July 13 I’ll be demonstrating the tool and presenting some tips and considerations for doing conversions.   They’ve given me two hours (!) to speak so I’ll be covering several other topics as well.



Evaluating a Data Story

I’m midway through Alberto Cairo’s new book “The Truthful Art” and finding it very stimulating.  It’s an interesting time to be a data scientist,  journalist or consumer of data.

“The Truthful Art” encourages us to use data truthfully and fearlessly, and provides processes and principles to do so.

This week I noted a new study published by the Center for Immigration Studies (CIS). A recent Presidential Executive Order asserts that the US is in special danger from travelers from seven particular countries. The order is controversial and is currently being challenged in the courts.

The CIS study found that 72 individuals from the “seven terror-associated countries”  were convicted in terror cases since 9/11/2001.  The study offers this number as evidence of the exceptional danger posed by immigration from the seven countries.

It seems like there may be more of story here than “72 terrorists from seven countries”. The study provided a link to the raw data used. I undertook an evaluation of the data and conclusions using some of the techniques I had just been reading about.

The date used to select cases in the study was “Conviction Date”.   A more meaningful date would be “Offense Date” Offense Date was not given,  but a “Charge Date” was available. I saw this  as a better proxy for when the offense occurred.  As shown in the table below, the number of days between Charge and Conviction can be quite substantial.  Using Conviction Date skews the offense into the wrong time period.

Days from Charge to Conviction

Median 75% Maximum
381 840 2407


Now instead of looking at “72”, I “broadened” my view of the data as Cairo would suggest.  What about the other countries?  Are there slices of the data that provide insight?

When I plotted two country groups — banned and others — over time, an interesting story emerged.  There are no defendants from the banned countries in the last three years of the study. This suggests that travelers from those countries may actually pose less risk than travelers from other countries.

After 9/11, US domestic counter-terrorism efforts were greatly expanded and overhauled.   The decline shown in the chart suggests to me that the current screening procedures are effective and continually improving.

I’m going to continue my journey through “The Truthful Art“.



Guest Speakers for Munich Masters Summit

We’ve got a couple of special  guest presenters lined up for the Munich Masters Summit for Qlik , 5-7 April.

Ralf Becher of TIQ Solutions GmbH, Qlik Luminary and  well known in the Qlik Community, will give a talk titled Spice your Qlik Sense app with Extensions and Widgets.  Ralf will present  use cases for extensions and showcase some of the incredible extensions he has created.

Nick Webster of Websy LTD will present  The Search for Sensey McSenseface.  Imagine building a dashboard simply by talking. Nick will demonstrate a natural language interface combined with Qlik APIs that allow a user to generate visualizations on the fly by asking questions like “show me sales for Germany”.  It’s pretty intriguing stuff.

Those special speakers are in addition to our already packed three day agenda.

I hope to see you in Munich. Can’t make Munich? Maybe you will join us  September 2017 in Boston.

Learn more about the Summit or register for Munich.




Document Open Processing

Summary: QlikView OnOpen Document triggers fire after the saved opening sheet is calculated.  That sheet may have some heavy calculations that slow the user’s opening experience.

Maybe you already know this, maybe not.

I commonly use a Document OnOpen trigger to make sure that my Document opens on the correct sheet.  I do this because I can be lazy or sloppy during development and save the document with the wrong sheet open.  What ever I saved with becomes the opening sheet.  I hate to reinstall an app just because I saved on the wrong sheet.

Are there any “gotchas” with doing things this way?

I just noticed (after all these years) that even if my OnOpen trigger opens a trivial or empty sheet, my document can take a long time to open.  Why? Because the data requires a lot of decompression? No.

It’s because objects on the saved sheet are getting calculated and then the OnOpen Activate Sheet is being applied!  If my saved sheet has some heavy objects, I may wait a while for those calculations to complete before the Activate Sheet runs and only then do I see output.

This is also true if I use a “Select in Field” OnOpen trigger to reduce my set to something like the current month.  The trigger fires after opening sheet charts are calculated with the saved state — which is generally all data.

Of course I can leverage this behavior by making sure I save my document with selections that represent a small set, and then refine using an OnOpen trigger.



The Document Analyzer Compare Tool

In my consulting practice, I’m frequently engaged to improve the performance of one or more QlikView applications.   In addition to an improved application,  I typically deliver a report of measured improvement and what was changed to achieve the improvements.

I use the Document Analyzer tool as my tuning workbench.  I’ve developed, and made available as a free download,  a companion  “Document Analyzer Compare Tool” that automates the comparison  and reporting of different Document Analyzer runs.  You can download both tools from the QlikView Cookbook Tools section.  Let’s look at the details.

Document Analyzer v3.0 introduced an option to save analysis results to a QVD.


When set to “Yes” a reload of DA will create a results QVD named

The results QVD qill be created in the same directory as Document Analyzer.qvw.

The “Optional Analysis Title” will be included in the QVD.  Use this label to identify the phase of your tuning or developement work such as “baseline” or “final”.

The DA Compare Tool can consume and report on these result QVDs.   To load results, open DACompareTool.qvw and enter a directory path on the “Main” sheet. Press the “Load Result QVDs” button and the directory will be scanned for result QVDs.

A summary table will show what result QVDs were loaded.  Note that each version of the DACompareTool requires results from a minumum version of Document Analyzer.  DaCompareTool v1 requires Document Analyzer v3.3 or later.  Results from versions earlier than the minumum will not be loaded but will be reported in log messages.

The “Trending” sheet will display gross performance metrics for all results loaded. You can if course filter which results appears on these charts using listboxes.

The remaining sheets are the “Compare” sheets, which compare two result sets.  The compare sheets use a naming common to “diff” programs — one result is selected as the “Left” side and another as the “Right”.  Left/Right reflects their position in the comparison charts.

On any compare sheet use, the “Select Analysis” button to select two analyses.

Close the Select Analysis dialog by clicking the X in the upper right of the dialog. This will lock your Left/Right selections.

Throughout the UI,  data associcated with “Left” will be light green and “Right” will be light blue.

Now that two results have been selected, various comparisons are available.  Items associated with only one side will be highlighted in the associated color. You can compare and filter

Object Calculation times:

Expression contents:

Script Tabs and Lines:

Table Names and Sizes:

The orange color indicates that both side contain the named table, but with a different row count.

I’ve also included a “Server RAM” sheet that can be helpful in computing the RAM required for a set of QVWs.  You will still need to add in estimated cache requirements, but it’s a good starting point.

“Concurrent User Sessions” is an input field allowing you to model an estimated number of concurrent users for each document.

DACompareTool looks best if the Open Sans font is installed.  If Open Sans is not installed, you’ll see a warning on the “Introduction Sheet”.   It will still work without Open Sans, just not as pretty You can download Open Sans from google fonts.

If you do  performance tuning, I hope you find the DACompareTool useful.  Let me know in comments here or on this site’s contact form if you have suggestions or questions.

I’ll be diving deeper into Document Analyzer and the Compare Tool at my Feb 2 “Document Performance Tuning” class and in an expanded Performance Tuning session with Oleg Troyansky at the 2017 Masters Summit for Qlik.  I hope to meet you at either or both for a deep dive into the principles and practice of Qlik performance.




Munich Masters Summit

I’m excited to announce our first 2017 Masters Summit for Qlik event in Munich Germany on 5-7 April 2017.



Designed for Qlik Developers who have basic skills and experience,  the Summit presents three days of intense hands-on sessions in topics such as Advanced Scripting, Data Modeling, Advanced Aggregation and Set Analysis, and Visualization Techniques, applicable to both QlikView and Qlik Sense.

You have some experience with Qlik, have taken the beginning courses. How do you ramp up to create more success with your Qlik program? Learn from seasoned experts, authors and world class presenters Rob Wunderlich, Barry Harmsen, Oleg Troyansky and Bill Lay.

In addition to the hands-on exercises, you’ll come away with many valuable sample files and documents. You’ll also get a chance to meet and network with Qlik Developers from around the world.

Our 2017 program features an expanded Performance Tuning section and additional content specific to Qlik Sense.

In four years over 800 Qlik Developers have attended eleven Summits around the world. Their feedback is overwhelming positive. Read about their experience here.

I hope you can join us in Munich to take your Qlik skills to the next level! Read about the details of registration here.



QVC Documentation for Qlik Sense

QlikView Components (QVC — should we rename it to “Qlik Components”?) documentation is now available in a Qlik Sense QVF format.   You can download it from github here:

After downloading the “QVC Documentation.qvf” file, you’ll need to copy the file to your Qlik Sense apps folder or drop it on Qlik Sense desktop window to view.

The “Using QVC” sheet provides general instructions for including the QVC library from either the file system or the web.


The “Details” sheet provides descriptions and parameters for all of the QVC routines.



Examples are not yet available in qvf format, as they are in qvw format. I’m working on that.

Have fun using QVC in Qlik Sense. If you have questions or need help in using QVC use the QVC Forum here.  I also teach online classes on QVC if you want a deeper education on the capabilities and applications of QVC.



Masters Summit for Qlik South Africa!

After presenting nine successful events in the US and Europe, we’re excited to travel to a new region. The Masters Summit for Qlik will be in Johannesburg, South Africa on 6-8 September, 2016.

Designed for Qlik Developers who have basic skills and experience,  the Summit presents three days of intense hands-on sessions in topics such as Advanced Scripting, Data Modeling, Advanced Aggregation and Set Analysis, and Visualization Techniques, applicable to both QlikView and Qlik Sense.

You have some experience with Qlik, have taken the beginning courses. How do you ramp up to create more success with your Qlik program? Learn from seasoned experts, authors and world class presenters Rob Wunderlich, Barry Harmsen, Oleg Troyansky and Bill Lay.

In addition to the hands-on exercises, you’ll come away with many valuable sample files and documents. You’ll also get a chance to meet and network with Qlik Developers from around the world.

In three years over 600 Qlik Developers have attended nine Summits in Europe and the US. Their feedback is overwhelming positive. Read about their experience here.

If you’ve been thinking about attending the Summit, you may find that the costs associated with attending in South Africa are optimal.  Read about the details of registration here.

I hope to meet you there!



The Cost of Preceding Load

Summary: While preceding load is a powerful tool,  the current performance penalty may cause you to reconsider using it for anything but the smallest of data sets.

I’m a big fan of the “Preceding Load” feature in Qlik Script. This is the facility that allows us to stack a LOAD statement on top of a SQL statement, or stack two or more loads to simplify coding.

I’ve written about the beauty of  preceding load in 2009 and 2014   HIC praised the feature in the Qlik Design Blog in 2013.

I’ve generally thought there is no downside to using  Preceding Load. I believed preceding load would always be faster than multiple load resident — even when presented with examples to the contrary (credit  to Sandro Krumbein, Bill Markham, others).

When I first tested the performance of preceding load in QV8 and QV9, I detected very little overhead.  With  QV11 and QV12, the penalty (for LOAD on LOAD) is extremely large, such that I currently avoid using preceding except in small data sets.

Let look at a simple test case, that of creating an additional field when loading a QVD.  Admittedly thiis is not a good candidate for preceding load, but it should be a viable test case.

Creating the field without preceding:

LOAD *, A&B as B2 FROM data.qvd (qvd)

Creating with preceding:

LOAD *, A&B as B2; LOAD * FROM data.qvd (qvd)

Both statements will result in a non-optimized load.  Here is time in seconds to perform each statement in QV version 9,  along with an optimized load for  comparison.

The preceding load takes 28% longer, a significant amount. However, back then I was more worried about the loss of the optimized load. That was a difference worth caring about!

Now lets add in the test results for versions 11 & 12.

Overall, a big improvement in total time vs V9, especially in the non-optimized load. But a significantly larger penalty when using preceding. Why?

Before I go on, I realize you may be wondering about the performance of a LOAD on SQL like:


My testing show this incurs approximately a 4% penalty, so not to worry for LOAD/SQL.

Back to LOAD on LOAD from QVD or Resident.  Wouldn’t it be great if we could spend only 4% more for the preceding, instead of the %230 in V12 above?

Where is the extra time going? I’ll simplify the test to create no extra fields. Just a

LOAD * FROM data.qvd(qvd);

For comparison, I try 1, 2 and 3 “LOAD *;” statements stacked on LOADs from QVD and Resident.

I see a large increase in the first preceding, followed by smaller increases for each subsequent LOAD.  The numbers suggest, contrary to popular teaching, that a series of resident loads may be faster than preceding load.

Where is that time going? Let’s look at IO and CPU counters for each each test.

The additional time all seems to be CPU related.  Interesting to me, the “Read Operations” count for the QVD Preceding seem to indicate that QVD is actually read using the optimized block IO technique.

What’s happening with that extra CPU time? Is it required or is it something that can be improved? I’ll try to ask R&D folks this question at the upcoming  Qonnections May 2.

In the meantime, let me know if you’ve seem similar or different results in your load scripts when using preceding load.




SF Bay Qlik Dev Group — March 16

Have you been to a Qlik Dev Group meetup yet?  Why not?  The meetings are free and take place all over the world.  At QDG high quality Qlik speakers like Henric Cronstrom, Donald Farmer and Alexander Karlsson offer insight and details of Qlik products.  Community presenters like Richard Pearce and Brian Booden offer development tips and show off wow extensions.

It’s a place to share technology, practices and tips in a non-competitive, no-selling environment.

I’m pleased to be hosting and presenting at the inaugural SF Bay Area Qlik Dev Group meetup on March 16 from 5:30pm to 8:30pm.  The location for this meeting is conveniently located next to the Montgomery BART station.  Please pre-register if you plan to attend.

In addition to introducing the QGD idea, I’ll be presenting “10 Qlik Performance Tips”.  Our agenda is in progress and we will announce additional presenters on the SF Region page as they are confirmed.

I hope to see you in SF! If that is not in your neighborhood, I hope you will join a meeting in your region of the world.  Learn more about the Qlik Dev Group on the QDG Homepage. 



Eat. Sleep. Qlik. Repeat