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.

-Rob

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!

-Rob

Important Note: The performance penalty discussed below was fixed in the Nov2017 release. There is no longer a penalty when using 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:

LOAD *;
 SQL SELECT * FROM ...;

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 *;
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.

-Rob

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

The next edition of the “Masters Summit for Qlik” takes place in Milan 5-7 April.  A discount of $300 is available for registration before 19 Feb.

Want to take your Qlik skills to the next level? At the Summit, Qlik Developers (you) will be immersed in three days of hands-on advanced and intermediate training covering topics such as advanced scripting, advanced expressions & aggregation, visualization, data modeling and  performance.

Join us as we enter our fourth year and our ninth event. What have you missed?

Training is led by four popular instructors, all well known as Consultants, Book Authors, Bloggers, Qlik Luminaries and longtime QlikCommunity contributors.

Barry Harmsen: Co-author of the seminal book “QlikView 11 for Developers” and founder of the Q-on Training Center.

Oleg Troyansky:  Author of  “QlikView Your Business” , QlikView veteran and one of the all time top contributors to QlikCommunity.

Rob Wunderlich (hey that’s me!):  Publisher of QlikViewCookbook.com and founder of the QlikView Components open source script library.

Bill Lay: Visualization wizard and everyone’s favorite presenter. Bill’s entertaining  presentation style makes any topic interesting and engaging.

In addition to the class material, we’ll have evening panel discussions, a guest speaker from Qlik Italy and plenty of opportunities to network with peers.

Read more about the Masters Summit for Qlik and Register here by 19 Feb to get that $300 discount! See you in Milan!

Bill Lay showed this one in his Tips & Tricks session at the “Masters Summit for QlikView” in San Francisco.

How do you show a currency symbol on chart y-axis values?  It requires two steps:

1. Assign the format, like Money, in the Number format.

2. Here’s the trick — set a value for the axis “Static Step”. 

The resulting chart will include the “$” on each value of the axis.

There is an odd bit about this behavior. If the chart is not tall enough to show the Static Step increment, QV will revert to not showing the “$”. I’ll reduce the height of this chart to demonstrate.

You can display units other than currencies,  for example meters. Use the text in the Number format like “#,##0 meters”.

One more odd bit to be aware of. Remember I said the text part will not display if the chart is too short? An exception is made if the chart is tall enough for every line on the axis to have a value. If every line is filled, the text is shown, regardless of the increment used.

Let me demonstrate with the Length chart. I’ll set the Static Step property to “1”.  As expected, the “meters” text will not display because the chart is not tall enough to show increments of 1 meter.

Now watch what happens when I increase the height of the chart:

As soon as every line of the y-axis contains a label, “meters” appears, even though the increment is still much larger than 1.

Join us at a future Masters Summit for more tips from Dr Bill. I will be presenting along with the rest of the Masters crew in New York City in September and Copenhagen in October. See you there!

-Rob

Last month  I made an appeal in this blog for developers to run a tool that would scan QVWs and contribute anonymous data to a study. Sixty one sites have submitted data so far. As promised, I’m making the results available for download and analysis here.

It turns out to be a pretty interesting data set — if you are into QV. The available data is limited by a few design points of the collection:

1. All returned data had to be absolutely anonymous. This required that counting be done in the scan. I could not bring back the actual expression text.

2. The scanner should not open the document. This means that script  was not examined. Only data available in the QVW XML was used.

As a representative sample of the QV world, the sample size is rather small and probably suffers from a significant selection bias.

Please also note some specifics of the counting. Functions in variables were counted, but only once. “Sum()” in a variable was counted as 1 use of Sum, even if the variable was used in multiple charts.

There are some interesting outliers in the submissions, which are intriguing to look at on their own.

So don’t use this dataset it for your PHD thesis but have fun with it. Here are some insights I found.

I would have never guessed that the number #2 Function was “If”!

I used  a couple of different ranking formulas throughout the analysis, but If() comes out a solid number #2 no matter how you look at it.

This led to another question (there’s that Data Discovery thing): Is there a relationship between the use if Set Analysis and the use of If()?  Not surprisingly, there is a strong correlation.

I’m of the mind that Set Analysis can be overdone and make an app difficult to maintain, vs building function into the data model. I don’t have an exact figure, but I frequently suggest to students that a max of 30% expressions with SA feels about right. It’s a really rough number meant to illustrate the concept that good data modeling is core.

Let’s assume that Swedes are excellent QV developers. There were three submissions from Sweden:

They look pretty good to me. There were submissions from experienced developers whose work I was familiar with and their SA usage tended to fall in that 30%-40% range as well.

What about Objects? I found a couple of surprises there.

I was surprised that Text Object beat out List Box for the top spot. But the biggest surprise to me was that Button made it in the top 5. I’d love to know what all those buttons are for. If you assume that many people use Text Objects with Actions as a Button substitute,  there are even more “Buttons” out there.

There is lots to explore in the data. The analysis has popup and drill down detail so explore with your mouse. And of course create your own visualizations and post your insights here.

Sites that submitted data can select themselves for comparison by selecting the unique submitted QVD name.

I will update the Survey Results app when I receive more data and of course, when you point out my errors J

-Rob

I was recently working with a customer developing training curriculum and the question was asked “what expression functions do  developers use most”. I thought it was a question worth getting a specific answer to. So I’m creating a community project to gather data on what QV functions and features people use.

Please help out by downloading this QVW and following the instructions in the file to scan  your installation’s QVWs. You only need to configure a file path and press reload. The scanner will gather anonymous information and produce a QVD that should be emailed to me per the instructions in the file.

• The scanner runs fast — about 60 QVWs per minute or better.
• The survey QVD contains only counts of function usage — such as how many time sum() is used.
• No private information is included in the survey QVD that you return to me. There are no qvw names, no userids, no expressions no fieldnames or data.
• I will make the collected data freely available to the community for analysis.
• I prefer that you run against your production or QA, just to eliminate the extra copies and backups that appear in a dev environment. If you can’t scan production, please go for the cleanest directories possible.

Why should you take the time to help me on this?

1. You may gain some insights from the data.

2. I help you out with free tools like Document Analyzer, QVPR Analysis and the QlikViewCookbook. Time to return the favor.

Thanks in advance!

-Rob

Version 10 of the QlikView Components scripting library added a number of new subroutines and functions when  Matt Fryer  merged his QVACB library into QVC.

Check out some of the Excel-like functions Matt has added;  NVL, DateDiff, InRange, Days360.  Also new workhorse routines like segmenting QVDs, emptying QVDs and loading Icons.

Along with Matt’s code, we also get Matt on the QVC team ! Matt brings fresh ideas and significant technical talent to the QVC project. You  may recognize Matt as author of the QV syntax plugins for Notepad++, UltraEdit and WordPress.  I’m pleased to welcome Matt as co-administrator and contributor to the QVC project.

The free open source QlikView Components (QVC) library can decrease the time you spend on scripting, improve the quality of your script. and let you share and leverage the experience of QV developers worldwide. If you have questions about QVC, use the QVC Forum here.

-Rob

I always enjoy Barry Harmsen’s popular-culture-and-Qlikview posts like last week’s “What QlikView developers can learn from The Karate Kid“.

If I got you back from reading Barry, here’s my bit of Mr Miyagi wisdom: “Focus not Filter”.

Green/white/gray in a listbox provides focus on the selected data while still keeping all data visible. The same concept can sometimes be useful in charts. Emphasizing selected data while keeping all data visible  maintains context and provides for consistent comparison.

Here are a few examples of techniques I use with QlikView charts to “Focus not Filter”.

All data and selected data can be shown side by side on a chart with the currently selected data emphasized.

Maintaining  constant scale as users make selections can be a good way to maintain context.  The image on the left is in the clear state, the image on the right  has 13 of 51 values selected. The y-axis scale remains constant which makes it clear to the user that they are now working with a relatively small number of schools.

“Graying out” unselected data is another favorite technique.

A variation on “graying out” is  maintain  color but mute unselected data and highlight selected data. In this line chart, unselected data is partially transparent and  lines for selected data are shown at 1.5 times standard width.

If you are trying to find the Karate Kid scene where Mr Miyagi says “Focus not Filter”, stop looking. It’s not in there, I made it up. But I’m sure Mr Miyagi would have said it if he used QlikView.

-Rob