Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
In this week's Qlik Design Blog I am pleased to share some tips from our own Mitul Vadgama of our Qlik Consulting Services team. Mitul was recently working at a customer site when he noticed something that seemed a bit off when analyzing the customer's data. On the surface after loading the data everything appeared fine - but once they started performing selections, Mitul was able to identify the specific cause with the help of Qlik's Associative Difference, the elusive <NULL> value.
The Problem
The customer's data model had about 300+ fields with a number of these fields being of a nominal data type (also known as categorical data). Put simply, I like to describe nominal data as the attributes in the data that don't carry a numerical value. Examples of nominal data are as follows:
Mitul found that when business users were selecting values in a nominal field it gave the correct result, however when they performed advanced selections such as using the the Select excluded option available in a select list, the results did not match up against what was expected. Thanks to Qlik's Associative Difference they were able to quickly and easily identify that <NULL> values were being excluded when the customer performed a Select Excluded selection. Yes, I know that sounds weird, the Select excluded option excluded <nulls> from the exclusion selection. (read-on and watch the quick video so this makes more sense to you 
)
Click to enlarge image
The Fix
One way is to use the NullValue variable and NullAsValue statement set in the Data Load Editor settings. (Qlik Help)
Syntax:
Set NullValue='<NULL>';
NullAsValue Field1, Field2;
In our example data we have <null> values in the StateProvince and OfficeStateProvince fields both in the Customers, and Employees tables. In the image above you can see that the <null> value is also not select-able in the filter pane.
Example:
Set NullValue='<Unknown>';
NullAsValue StateProvince, OfficeStateProvince;
After adding these settings to the Data Load Editor we were able to get the correct results as the <null> values were now being taken into consideration.
Click to enlarge image
Companion Video: Replacing Null Values in Multiple Fields
NOTE: To increase resolution or size of the video, select the YouTube logo at the bottom right of the player. You will be brought directly to YouTube where you can increase the resolution and size of the player window. Look for the 'settings' gears icon in the lower right of the player once at YouTube.
We hope this post has shown you another efficient way to handle <null> values in your Qlik application.
I'd like to thank Mitul for his valuable contribution to the Qlik Design Blog.
- For further reading on null handling check out this classic blog post by Henric Cronström:
https://community.qlik.com/docs/DOC-3155. - For a full list of services offered by Qlik Consulting check out: http://www.qlik.com/us/services/qlik-consulting.
About Mitul: Mitul is a member of our Qlik Consulting Services team in the UK. He has worked on a variety of consulting projects using both Qlik Sense and QlikView with many enterprise customers. His passion is transforming data into valuable business insights, knowledge sharing, and enabling customers to get the most value out of Qlik products.
Can't see the video?
Download the .mp4 and watch from your computer or mobile device.