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

You’ve probably heard of the QV “Dual” function and may have seen some interesting uses for dual(). One of my workhorse applications of Dual is flag fields.

In script, we might create flag fields such as:
  if(Not IsNull(ShipDate), 1, 0) as Shipped

This creates the new field “Shipped” that contains 1 (if the corresponding ShipDate has a value) or 0. This is a useful flag for chart expressions like “if(Shipped, …” or “sum(Shipped)” or “OrderTotal * Shipped “.

If we display the flag in a Listbox or Table, it looks ugly. 0’s and 1’s are appreciated only by programmers.

If instead of 0/1 we assign string values like  “Yes”, “No” the display would look better.

But if we use string values, we lose the ability to use numeric expressions. Instead we would need to use slower string expressions like “if(Shipped=’Yes’,…)” and “count(Shipped).”.

Wouldn’t it be great if we could have the best of both worlds? Strings for display and numbers for convenience and performance? This is where dual is useful.

A quick review of the function from Help:
  dual( s , x )
  Forced association of an arbitrary string representation s with a given number representation x.

What this means is that a field value will have both string and numeric values — a dual nature. Which type is used  — string or number — is dependent on the context where the field is referenced.

Let’s define that Shipped flag using dual:

if(Not IsNull(ShipDate), dual(‘Yes’, 1), dual(‘No’, 0) ) as Shipped

We are still assigning one of two choices to Shipped. The assigned values have a dual nature. Qlikview will use either the string or numeric value automatically based on how the field is referenced.

In a display context like a Listbox or dimension, the string value (“Yes/No”) will be used.
In a numeric expression like “sum(Shipped), the numeric value (0/1) will be used.

What about the “(If(…” test? QV will choose a numeric or string comparison based on the presence of quotes. All three of these examples are valid:

If(Shipped, …
If(Shipped=1, …
If(Shipped=’Yes’, …

In practice, I may create many 0/1 flags in a single script. Rather than clutter the syntax with a lot of repetitive dual(), I use a mapping approach.

First I define my map:

YesNoMap:
MAPPING LOAD num, dual(str, num) INLINE [
num, str
0, No
1, Yes
];

Then I identify the fields I want to assign to dual() with MAP USING statements:

MAP Shipped USING YesNoMap;
MAP Audited USING YesNoMap;

Later in the script I create the flag fields using the simple 0/1 assignments:
if(Not IsNull(ShipDate), 1, 0) as Shipped

MAP USING will translate the 0/1 into the desired dual. This approach also makes it easier to globally redefine my display values from “Yes/No” to “Y/N” or “Si/No”.

-Rob