Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
Michael_Tarallo
Employee
Employee

einsteinshow.jpgIn 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:

nominal-scales.png

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.

mitul.png

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.

4 Comments
ecolomer
Master II
Master II

Very usefull

Thank's for sahring

Saludos,

Enrique Colomer

2,133 Views
leandro_gocosta
Contributor III
Contributor III

Thank you for your contribution.

0 Likes
2,133 Views
kkkumar82
Specialist III
Specialist III

Adding to the information, if the data is read from csv or text files the above two commands won't work because Nulls are converted into spaces however if we use Set NULLINTERPRET ='', this would work once again along with the two other commands,

Set NullValue and NullAsValue

2,133 Views
framacdev
Contributor III
Contributor III

Keep also in mind that the NullValue - NullAsValue structure will not work when you associate tables by using the QlikView script. NULLs have to reside on the data source table

2,133 Views