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
Excellent Rob. Thanks for sharing. Add this to the tips for next years Masters Conference!
Hey Rob,
Nice tip. But isn’t it simpler to use applymap with a third parameters for the missing/null values?
For example applymap(‘YNMap’,Shipped,’N’) as Shipped
Instead of creating a mapping load for the missing/null values, you create one for the existing ones. For the Shipping field it would in this case be ‘Y’,’Y’.
Hi Frank,
My method handles the case where there are null values created by the join of the shipments. Using ApplyMap() in the join would only populate the rows that had shipments.
You could use ApplyMap() in the final load, but that would require either listing every field in the table, or creating a new field followed by a RENAME/DROP. Much more complex than my solution I think.
-Rob
but this will work if you want to put same value in the field. If I want to put different value, then what should be the code?
Hi Rob,
Thank for this tip.
A noob question, why did you use the Autogenerate keyword?
Thanks,
Antoine
Hi Antoine,
The autogenerate 1 means “run this load statement 1 time”. Since there is not data source — a database or file — the autogenerate serves as the “FROM” for the LOAD.
I have a table with fields that return values as 1,2,3,4,5,6,7, and I need to group the values 1,2,3 as’ new name ‘and 4,5,6 and 7 with’ other name ‘, as I do?
Create a MAPPING table with the “from” and “to” values.
-Rob
Is there a performance advantage of using this method opposed to say an If statement combined with an IsNull during the load? Thanks.
A quick test expanding the blog example to 10M rows shows a slightly faster execution using if(). It’s not enough of an advantage that I would rely on it or give up the convenience of using LOAD *.
Hi Rob,
It may be worth pointing out for those below expert level (like me) that this method won’t work when loading from a qvd. In such a case you need to follow your advice for mapping to an existing table after the qvd table is loaded.
If I understand it correctly this is because qvd loads are like blocks of memory being moved rather than individual lines being processed and so there is no opportunity for the mapping to be applied. Of course best practice would be to have the mapping applied to the data prior to being stored to qvd.
Thanks for your blog, it’s a terrific read.
That’s an excellent and important comment Andrew. Global mapping from a MAP USING statement is not applied during an optimized QVD LOAD. It’s an undocumented an silent behavior.
I need to correct something I said a minute ago. We can map a qvd load but only if it is unoptimised which we can ensure by adding the WHERE 1=1 statement or similar.
cheers
Andrew
Please, how do I set a default value to null, when null values are generated by non matching association? Is it possible? For example: I have table A and table B with the same field %LinkKey, so they are both conected. But, when put some field from table B in the table A visualization, some values became null, because, ofcourse, not all values are matching… can you help me?
All visualizations have an option to suppress or include null values. In the case of a QV text table, you can also assign a value for null values on the presentation tab,
Thak you for your ansewer. In Qliksense, do I have this option? Or some workaround to achieve what I need?
What type of chart are you using?
I’m using a simple table, like one in your example… It’s just a simple report and my table is exactly the first one in your article, the only difference is that my null values are generated by drag a field from table B to table A vizualization. There is no problem if there no solution for this… and appreciate your time… Thank you!