All posts by Rob Wunderlich

Preceding Load Performance Update

Summary:  Preceding load used to slow down your script. but no more. Beginning with QV Nov 2017,  preceding load has no performance penalty.

I’ve posted several times about the elegance of preceding load.  I’ve also written about how preceding load can make your script run significantly slower.  Good news! Beginning with QV release Nov 2017 (12.20) the performance penalty has been eliminated.

To demonstrate  the improvement, let me start with  some test results from QV12.10 SR8,  prior to the improvement.

 

Test 0, the first bar, indicates the time in seconds to perform an optimized load of  a 20 million row QVD.  Test 1, which follows, is loading the same QVD but with the addition of two new calculated  fields in the same LOAD statement.  The calculations are trivial, so the increase in elapsed time is mostly due to the loss of the optimized load.

Test 2 creates the same calculated fields using preceding load and you can see the dramatic increase in elapsed time.  Test 5 adds a “LOAD *” to the preceding load stack and again shows a large increase in duration.

Tests 3, 4 & 6 repeat the same tests using Resident as the source instead of QVD.  Once again, a significant increase in duration when preceding is used.

I’ve been running this same test suite for several years across multiple QV releases, different machines and varying datasets.  The results are generally the same.

The problem, as explained to me by Henric Cronström and confirmed by my own observations, is that the preceding load code  uses only a single processing thread.  So while tests 1 & 3 above will use multiple threads, tests 2,4,5,6 will use only a single thread.   One way to think of this is not that preceding load runs slower, but that non-preceding load runs faster.

I never did understand why Preceding-Resident ran slower than Preceding-QVD, but I no longer care!

Here I add test results (in red) for QV Nov 2017 SR1 (Qv 12.20) .

You can see optimized QVD (test 0)  is about the same.  Adding calculated fields (test 1) is  slightly better between releases.

What is really significant is there is no longer any increase when using preceding load.  Further,  Resident performs faster than QVD as I would expect. (Note both tests used an SSD drive).

This is all great news as there are many cases where preceding load can help make your code more maintainable and understandable.  I hated to choose between clarity and performance.

What about Qlik Sense?   I’ve confirmed that Feb 2018 Desktop exhibits the new “no-penalty” performance.  I don’t know about previous releases.

No reason to fear preceding load!

-Rob

Share

Distribution Plot in QlikView

Qlik Sense added a Distribution Plot visualization in the June 2017 release.   QlikView does not have a specific chart type for distribution plot, but you can achieve the same with a scatter plot.

The trick is to set the Y value (Expression #2) to a constant value such as “1”.  Here’s a distribution of Life Expectancy by Country (source: WHO 2017).

Dimension: Country
 X-Axis: =[Life Expectancy]
 Y-axis: =1

It works, but it’s difficult to understand how many points overlap.  You can switch the Style to the outlined ball similar to Qlik Sense and that helps.

I find a more effective technique is to add some transparency into the color.  Overlapped points will result in a darker color.

You can also highlight points using set analysis or alternate states.

 

Adding reference lines such as  Quartiles can provide additional understanding.

To add a second dimension e.g.  “Sex” (values: Male,  Female, Both)  replace the fixed Y-axis expression with an expression that generates an index number for the values.

=Dual(Sex, FieldIndex('Sex', Sex))

That will assign Y-values 1,2,3 to the Sex values.  The Dual() will ensure the text value will show in the popup. The Y-axis  will still display a numeric value so I’ve hidden the axis.  That leaves us without labels for the three lines.  We can either create labels using text-in-chart or use a color coding scheme.

Distribution plots can be oriented vertically by using a fixed X-axis. If you’ve used my ScriptRepository tool, you may recognize that the search results scroll-guide (the yellow dots) are a narrow scatter plot.

-Rob

Share

QDG Guru Day Inspiration

Lots of great presentations at the QDG Guru day in London last week.  Every talk gave me something to think about or explore futher.

Bruno Calver’s discussion of cohort / cell analysis (from his excellent white paper “Data Literacy –5 Practical Tips“)  and Patrik Lundblad’s discussion of multivariate analysis blended together (mashed up?) in my mind to inspire the example below.

The scatter chart below plots US agricultural commodity exports in year 2017.  Dimensions are Product and importing Country.   Taking a clue from Bruno’s talk,  I’ve concatenated Country & Product as the dimension to facilitate “cell analysis”.

X-axis is the absolute quantity in Metric Tons, Y-axis is the quantity normalized to population (thanks Patrik & Bruno) .

“Mexico-Corn” looks interesting. What should happen when I select the Mexico-Corn bubble?  Should the chart filter to show just that Country & Product?  Probably not, as that would be be a single bubble.

Regular readers of my blog know that I’m a fan of understanding user selections not as a filter, but rather a focus.

More likely I’m interested in exploring both Mexico and Corn as separate, but inter-related variables.  With a bit of set analysis I can display and color both series.  How about this for the results of clicking Mexico-Corn?

This strikes me as useful.  I can the understand the position of Corn in imports to Mexico, and the position of Mexico across all Corn importing countries.  See the chart subtitle for an explanation of the color encoding scheme.

Thanks again to to all the Guru Day speakers for the stimulating talks and the inspirations. Let’s do it again soon!

-Rob

You can download the example qvf here.

Share

Masters Summit Prague

The 14th edition of the Masters Summit for Qlik will take place in Prague on 3-5 April 2018.  In this three day hands on education event our goal is to “Take your Qlik skills to the next level”  making you more productive and increasing the business value of your QlikView or Qlik Sense applications.

Through lecture,  hands on activities and takeaway code samples,  the Summit will expand your knowledge with advanced techniques and understanding of the core skills required in all Qlik application creation:

  • Data Modeling
  • Scripting
  • Advanced Aggregation & Set Analysis
  • Visualization.

In addition to core topics, we’ll have 1/2 day workshops on performance tuning and an introduction to creating Qlik Sense mashups using APIs.  See the complete agenda here.

Our evening guest speakers, networking events and optional lunchtime lectures fill out the program with additional content and lively discussion.

Our panel of five presenters are well known as authors, educators,  Qlik experts and members of the Qlik Luminary and MVP programs.

Have you taken basic Qlik training and/or worked with the product for a while?  Do you find yourself struggling with data modeling questions such as slowly changing dimensions and rolling time analysis?  Syntax for aggregation questions like “what products do top salesreps in each region sell?”  When do I need “$()” and when do I not?  More self assessment to determine if the summit is right for you can be found here.

I hope you can join us in Prague to take your Qlik skills to the next level.  The early bird registration discount is available until 2 March.  Event details and online registration.

See you there!

-Rob

Share

LET, SET, Quotes

Summary: In Qlik script SET is often a better choice than LET, even when the value contains quotes. 

I sometimes see the LET script statement used when SET would be syntactically  easier and more readable.

A brief review:  SET assigns the given parameter as-is to the variable,  LET treats the parameter as an expression and assigns the evaluated result to the variable.

SET x = 1+3;  // x is "1+3"

LET x = 1+3; // x is "4"

I frequently see a variable assignment like this:

LET eSales='sum(Sales)';

eSales stores an expression that will be used later in charts.  It could also be written (simpler in my estimation) as:

SET eSales=sum(Sales);

So far just a matter of style, but the difference becomes clear when we have quotes as part of the string, for example, “Region={‘US’}”.   As LET requires a quoted string,  embedded quotes require some sort of escaping.  In QV10 and earlier, a common way to write this with LET would be:

LET x = 'Region={' & chr(39) & 'US' & '}';

Not real pretty. Many people carry over this style even though QV11 introduced two single quotes to represent an embedded single quote.

LET x = 'Region={''US''}';

Easier to read for sure.  But I think it’s even easier with SET.

SET x = Region={'US'};

That’s it. No special escaping required, just type it as it should be.  What about those quotes? Shouldn’t SET strings be enclosed in quotes?

I find the documentation on SET to be thin, but here is the rule as I understand it.

Single or Double quotes in a SET statement require no special treatment as long as they are balanced (even number of quotes).

SET x = Region={'US'},Product={'Shoe'};  // Valid

SET x = Region={"U*"},Product={'Shoe'}; // Valid

SET x = I won't go;   // Invalid

If the quotes are unbalanced (odd number), then the entire string needs to be enclosed in quotes or brackets.  Use double quotes if we are enclosing single quotes.

SET x = "I won't go";

SET x = [I won't go];

I always favor SET over LET unless I truly want an evaluation.  An exception to this is the string “$(” which will trigger an Dollar Sign Expansion, even in SET.

-Rob

For more on character escaping in Qlik from HIC see https://community.qlik.com/blogs/qlikviewdesignblog/2015/06/08/escape-sequences

Share

Spring Holiday Recommendations

Summary: This post has nothing to do with Qlik. It’s a reach out to the community I have built over the years soliciting holiday recommendations.

I have a two week holiday break between the Masters Summit for Qlik in Prague 3 April and Qonnections 23 April.

My wife Linda favors warm so we are heading south.

Through my experience with this site and the greater Qlik Community,  I’ve been  blessed to travel and make  friends all over the world.  So now I’m reaching out to that larger community with a question that has nothing to do to with Qlik or BI.

Should we spend two weeks in Southern Spain or one week in Spain and another in Morocco?

I’m expecting to hear from my Portuguese, Greek and other friends as well 😉

Where would you go with those two weeks in April?

-Rob

 

Share

Get the Numbers Right

I travel a lot for business and personal reasons. I’ve used TripIt (owned by Concur) for several years to plan and manage my trips.

It’s a  great tool, can’t image living without it.

Today TriptIt sent me a lovely graphic email summarizing my 2017 travel.  The design was  a very fashionable  card layout with share-to-social links.

Pretty cool, but for one problem.  It’s not my data.

Yes, those are definitely not my travel stats.

When I teach Qlik application development, a common  question raised by students or other newcomers is: “What’s the most important skill to master?  Scripting?  Expression writing?  SQL? Pre-attentive attributes? Color rules?”

My answer is always the same: “First, get the numbers right.  Second, keep the numbers right”.

It doesn’t matter how it looks or how fast it calculates if the results are wrong.

Join me at the Masters Summit for Qlik in Prague 3-5 April where in addition to a full range of Qlik Dev topics, we’ll be discussing automating quality.  I’ll be demonstrating tools to automate dashboard validation and continuous quality monitoring.

-Rob

BTW I emailed the TripIt folks about the error, they’ve promised to look into it.  

Share

Hiding Mashup Objects

I’m really appreciating the Qlik Sense Mashup facility.  In the simplest case, mashups are when you embed Sense charts in a web page.  Super easy with Sense.

Perhaps you’ve created some charts in your app that exist solely to serve the mashup.  What if  you want to display these charts in the mashup but you don’t want them showing in a sheet when the app is viewed via the hub?

How to make these charts available to the mashup but not appear in the “app”?  Simple.   After creating the chart, make it a Master Visualization and then delete it from the sheet.  The mashup can use the Master Viz Id to reference the object.

If you are using the Dev Hub Mashup editor, note that Master Visualizations appear at the top of the “Sheets and Objects” list, before the first Sheet.

 

 

 

 

 

 

 

 

-Rob

Share

QViewer Acquisition

I’m pleased to announce that I’ve acquired the excellent QViewer tool from Dmitry Gudkov effective January 1.  I’ll be marketing, developing and supporting QViewer going forward.

QViewer has a stellar reputation as a must have tool for Qlik Developers.  I’m pleased to be taking over such a fine product.

This acquisition is one step in my plan to focus more on software development during 2018.  Stay tuned for other announcements during the year.

Existing licensed QViewer customers should email future support requests to support@panalyticsinc.com.

Happy New Year!

-Rob

Share

DevTool Extension for Qlik Sense

One of the benefits of attending the Masters Summit for Qlik is networking and the things you can learn from discussions with peers.

At the Boston Summit, I was fortunate to meet Erik Wetterberg, formerly of Qlik R&D and known to me as the author of the qsVariable and DevTool extensions.  I’ve been a fan of the DevTool extension for some time and had a nice chat with Erik about potential enhancements to DevTool.   Since the summit Erik has made some updates and accepted new function I’ve added to the tool.

DevTool provides some functions that are useful during Qlik Sense app development and debugging.  Download the DevTool zip from the dist folder here https://github.com/erikwett/DevTool and install as you would any other extension.

Add  the extension to any sheet and it will create an Action Button (AB) to the lower right corner of the sheet.

You don’t have to leave the extension in the app, the AB will remain until you close the app.  I usually don’t leave it in the app, I just add it and then remove by double clicking DevTool in the assets panel and follow that with a Ctrl-z undo.

You can leave it in the app if you wish.  There are some considerations discussed below for leaving it in a published app.

Clicking the AB will toggle a tooltip above each sheet object that initially displays the objectid.  The objectid is useful when building mashups or reviewing diagnostic data.   As selections are made and the objects are recalculated, the tooltip will add a line showing the time in milliseconds for the current calculation as well as the max calculation time for this object.

 

Click the “properties” button on the tooltip and this object’s json formatted properties will display in a popup.  Use the copy-to-clipboard button to copy all or selected property lines to the clipboard.

Right-Click the AB to open a context menu that provides for:

  • Exporting script to a text file.
  • Importing (replacing) script from a text file.
  • Exporting variables in script or json format.

The extension works equally well in desktop or server.  Some things to think about before including it in a published app.

  • Users normally can’t see the load script and variables in a published app, but they can with this extension.
  • Although the script import works without error in a published app, the change to script is not saved so effectively nothing has been updated.

For myself, I’ve been thinking of this as a tool to use during development only and have not been including it in published apps.  I’d be curious to hear if someone has made a different decision.

The extension buttons use a google font. I note that many servers are blocked from the internet. If this is your case, you will see red dots for the buttons instead of proper symbols.  The buttons are still fully functional.

Maybe I’ll see you at the Masters Summit in Prague  where you might find your own interesting collaboration.

-Rob

Share