02035736917

NULL is nothing but no value. Null Values will be visible as dashes in QlikView Table Box.
In this article I am going to explain How to handle Null Count in QlikView and Qliksense with different types of Methods using one example for easy understanding.
Find out Nulls in a field is a little bit tricky until you understand the tricks and tips which can make it a little simpler.
For example, I want to calculate the Count of Null Values Present in Value Field.

Method 1:
In the above Example, there is Total 17 Null values under Value Field.
To get the Null Count 17 use the below expression. As per this expression when the value is Null and value is equal to Zero then the count will be produced by this expression.

=Sum( IF(ISNULL(Value) OR LEN(trim(Value))=0 OR Value=0,1,0))

To avoid Zero values and to Count only Pure Null Values then need to use the below expression.
=Sum( IF(ISNULL(Value),1,0))

To get non Blank Values then need to use the below expression.
=Sum( IF(ISNULL(Value) OR LEN(trim(Value))=0 OR Value=0,0,1))

Method 2:
Counting Nulls in QlikView and QlikSense is made easy using the NullCount() function which pretty much does exactly as you'd expect. It can be used in both the script and expressions and the syntax is very straightforward:
=NullCount(Value)​​​​​​​

Method 3:
Using MissingCount()
=MissingCount(Value)​​​​​​​

Difference between NullCount() and MissingCount()
The first thing you want to keep in mind is that QlikView and Qlik Sense uses dual values to store all data.
The dual value consists of two components, the value you see in the application and an underlying numerical value.
If you have a value like 1.6, there might be an underlying value that is equal to 1.5875.
If you have a thousand separator the visual value could look like 1,600, while the underlying number is 1600.
When there is no underlying numerical value, the value is considered as a text.
There can never be an underlying numerical value, without a text representing it.
When there is no text, there is no value. This empty space is referred to as a NULL value. NULL is typically presented as a dash in QlikView.
NullCount() will count all values that are defined as NULL.
MissingCount() will count all values without a underlying numerical representation, in other words all NULL values and all texts.

Method 4:
If the value is Null or if the value is like blank/Space (‘ ’) then use the below expression
=Count(if(IsNull(Value)or Value=' ',1))

Method 5:
Using NULLASVALUE() in the Script also we can calculate the Null Count. The NullAsValue statement specifies for which fields (here Value Field) that NULL should be converted to a value.
Use the below code in the back end. Here all the null values will be converted to NULL Text

NULLASVALUE Value;
SET NullValue = 'NULL';
LOAD
ID,
Value
FROM [C:\QlikShareFolder\NullCount.xlsx]
(ooxml, embedded labels, table is Sheet1);


In the Front end to see the Null count use the below Expression
= Count({< Value = {'NULL'} >} ID )

Method 6:
In this method we are creating a Flag with True or False. Here 1 represent the Blank records count and 0 represents the Non-Blank records count.
Use the below code in the back end to create a Flag

LOAD
ID,
Value,
IF(ISNULL(Value) OR LEN(trim(Value))=0 OR Value=0,1,0) as Flag
FROM [C:\QlikShareFolder\NullCount.xlsx]
(ooxml, embedded labels, table is Sheet1);


In the Front end to see the Null count use the below Expression
=Count({<Flag={'1'}>}Value)