As a QV Consultant, I split my time between delivering training and QV Practice.  I like both sides of the business, but I particularly enjoy contributing to the excitement of using QV that results from training.

During a lunch break today, I mentioned the Masters Summit for Qlikview to one of my students. He asked “What does it take to become a Master?”. Of course I answered “Attend the summit! “.  But it reminded me of a list I present when delivering  Qlikview Developer (Scripting & Data Modeling) training.

Most of us experienced instructors augment the standard curriculum with our own real-world experiences. When teaching scripting, in addition to surveying the range of scripting statements and modeling solutions, I emphasize three specific scripting features as “Ninja skills” — script features whose mastery makes you deadly effective. My “Scripting Ninja” list is:

1. MAPPING LOAD. Understanding how to create a Mapping table. How to use that table in all it’s forms — ApplyMap(), MAP USING, RENAME USING and the other metadata USINGs — TAG and COMMENT.

2. IntervalMatch — has many uses  including Slowly Changing dimensions, Currency Rates, Dimension Bucketing to name a few.

3. Preceding Load — can be used to create powerful parsing scripts and simplify the maintenance and accuracy of script. Here’s a primer on Preceding Load.

Those are the three special QV scripting features that stand out for me as exceptionally powerful and well worth mastering. Are there others you would add to the Ninja list?

-Rob

Gather any group of enthusiasts together over a pint after work and inevitably there will be debate over what I call a “Pub Question”. Comic Fans will argue who would win in a fight between Batman and Spiderman, Web Programmers will debate the merits of Java vs .Net. For a group of Qlikview Script developers, the question may be “what’s the fastest script to get max value of a field from a QVD?”.

Qlikview has virtual Pubs in the Community Forums and blogs where these questions get raised. In a recent blog post http://qlikviewnotes.blogspot.com/2013/08/super-fast-method-to-retrieve-qvd-high.html I got some comments about this very question, including a couple of methods I had not tried before.

Let’s dispense with the Resident Table first. The fastest method for a Resident Table is loading the FieldValue()s.

This is much faster than loading From Resident like this:

Here’s a comparison of the two:

For the test, I use a table with 10M rows and 7 fields. I vary the cardinality of the “Id” field from 1:1 to 100K:1. The x-axis of the chart shows the factor and the number of unique values (rows / factor).

The left side of the chart represents the maximum unique values at 1:1. The rightmost side of  the chart is least unique with only 100 values.  These ranges represent some real world cases such as:
1:1 — Primary Keys
2:1 — Timestamp values
1000 values — Date values

For a 1:1 case, there is not much difference between the FieldValues and Resident methods. However, as the number of values decrease, the FieldValue method follows in a linear trend. The Resident stays fairly constant as the same number of rows are being read.

Now on to the answering the “Fastest” when loading from a QVD. The answer, like all good Pub Questions, is “it depends”.  I ran timing tests on the following methods:

QVD :

QVD-*/FieldValues:
LOAD * FROM (qvd);
LOAD max(FieldValue(‘Id’, recno())) 
AUTOGENERATE FieldValueCount(‘Id’);
QVD-DISTINCT-Field/FieldValues:
LOAD DISTINCT Id FROM (qvd);
LOAD max(FieldValue(‘Id’, recno())) 
AUTOGENERATE FieldValueCount(‘Id’);
QVD-DISTINCT-Field/Resident:
LOAD DISTINCT Id FROM (qvd);
LOADmax(Id) Resident table;
QVD-Field/FieldValues:
LOAD Id FROM (qvd);
LOAD max(FieldValue(‘Id’, recno())) 
AUTOGENERATE FieldValueCount(‘Id’);
QVD-Field/Resident:
LOAD Id FROM (qvd);
LOAD max(Id) Resident table;

Everything but the first case will be an Optimized QVD Load.

Here are the test results.

Surprisingly, the QVD only case is the winner for Factors 1-3, even though it is an un-optimized Load. This is not so surprising when you consider how data is stored in a QVD. The field values are stored in a symbol array, just like a qvw in RAM. Reading a single field would require only reading in those symbols. Combining the max() logic with this step would be efficient.

Note that the LOAD RESIDENT operation doesn’t benefit much from a deceasing number of values. At the 1:1 end, all techniques are processing the same quantity of 10M values. As we move to the right, LOAD RESIDENT continues to process 10M values (rows) while the other techniques benefit from lessor quantities.

Let’s drill in a bit. I’ll exclude the QVD-Field/Resident test (for scale) and narrow the Factors to the middle stuff, 4 – 1000.

And here’s the total time for those selections.

So…if I had to pick the “fastest”:
– For 1:1 cardinality, like Primary Keys or revision numbers, direct from the QVD is fastest.
– For most other general cases, Loading just the field and then processing Fieldvalues is fastest.

Of course, there are a number of factors beside the data profile that can impact the results. Disk speed, number of cores, etc. I’d love to hear if you have different experiences.

My colleagues at the upcoming October 9 Masters Summit for Qlikview in London and Barcelona may have some thoughts of their own on this question. In addition to core topics, Oleg is presenting “Performance Tuning” and Bill is presenting “Tips & Tricks”. Both sessions should provide some practical pointers for speeding up your apps. There is still time to register for the Summit. If you attend, perhaps we can continue this discussion at an evening “Pub Session”.

-Rob

A clever customer reminded me of a useful script technique this week. Here’s the scenario.

A dashboard script was loading all fields from a qvd as:
LOAD * FROM data.qvd (qvd);

One of the fields, “Shipped”, was a flag field that contains the values 0/1. It became apparent in the dashboard UI that this would look better as a Dual() field with display values of Yes/No. It was agreed that the QVD should be modified to create a Dual(), but it will be some time before that change can be made. In the meantime, the change should be made in this dashboard script.

The Shipped field could be made a Dual() by modifying the QVD load statement in one of two ways.

1. Instead of “LOAD *”, list each field and apply the Dual() function to the Shipped field.
2. Add an additional field “Shipped2” and rename/drop to replace the Shipped field.

Both of these techniques share the downside of making the QVD load un-optimized, losing the speed advantage of optimized load.

Another possibility would be to create a Map with Dual() types and MAP the Shipped field:
MAP Shipped USING MyMap;
However, global mapping does not occur for optimized loads. To make the mapping work, you would have to force an un-optimized load.

Is there a way to change Shipped to Dual() while retaining the optimized load? Yes — here’s one way;

temp:
LOAD Dual(str,num) as Shipped INLINE [
str, num
Yes, 1
No, 0
];

data:
LOAD * FROM data.qvd (qvd)
;

DROP TABLE temp;

Before loading the QVD  we load a temp table with the field “Shipped” using the correct dual() values. The temp table is later dropped after the QVD is loaded. The key to making this effective is that you must uncheck “Survive Reload” for the Shipped field in Document Properties -> Number.

It’s an interesting technique and the extra blocks of code can be removed when the Dual() is eventually added to the QVD.

-Rob

It’s common to have a Qlikview table that contains null values for some fields. The nulls may be a result of source data nulls, QV Joins or Concatenates. Regardless of how the nulls were created, in this post I’ll demonstrate an easy to maintain global technique for replacing all nulls in a table with default values.

Consider this sample table:

The table has been created through a mix of Join and Concatenate. It’s data looks like this:

Note the null ProductDesc  because there is no match on ProductId. The BackOrdered field is null for orders that didn’t come in from the BackOrder table. Shipped is null for orders that had no match in a Join with the Shipments table.

What if we want to have meaningful values for the nulls, perhaps to make them selectable? We may have been able to assign defaults as we built the table, but that can be a hassle. Let’s do it the easy way after the table is built.

First we build Mapping tables for the output values we want to assign. For BackOrdered and Shipped, null should be ‘N’. For null ProductDesc, we want to assign the string ‘MISSING!’.

Recall that a Mapping table has two columns — column1 the value to be mapped (null in this case), column2 the value to be assigned. A value that does not appear in the Mapping table remains unchanged. Let’s create two Mapping tables for the two output values.

YNMap:
MAPPING LOAD 
null(), ‘N’ AutoGenerate 1;


MissingMap:
MAPPING LOAD 
null(), ‘MISSING!’ AutoGenerate 1;

Next we connect fields to the mapping tables with MAP USING statements. The statement identifies the field and Mapping table that should be used.

MAP Shipped USING YNMap;
MAP BackOrdered USING YNMap;
MAP ProductDesc USING MissingMap;

The mapping will be applied when the field value is created. So how do we apply the map to an already existing table? We make a copy of the table. That will cause the latest mapping to be applied

Orders2:
NoConcatenate   // This is important! We want a new table!
LOAD * RESIDENT Orders;
DROP TABLE Orders;  // Drop the original table

And now the  data looks like this (bold added).

This is a simple method to make your data as robust as possible for the user experience in the front end.

-Rob

Here are a few items I hope show up as script and development features in Qlikview.next.

1. Script scoped variables. I use a lot of variables in script and it’s a hassle to have to delete them at the end of the script before they get promoted to the UI. I’d like to have a variable type that gets automatically dropped at script end. Perhaps by naming pattern like:
  SET ScriptOnlyVariablePrefix=’_’;

2. Function blocks in script. Not variables with parameters, but blocks that can execute multiple script statements and return a single result.

3. Allow the CALL statement (and other control statements) to span multiple lines. 

4. Regular Expression test and match functions. 

– A few more that are not script, but development related:

5. A chart function to read Field and Table Comments. 

6. Menu command to open the script logfile. 

7. Support cases remain open until a bug fix is delivered — not closed when a bug id is assigned. 

-Rob