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
Posts
