Qlik Sense Null Functions – IsNULL and NULL

1. Objective

In our last Qlik Sense Tutorial, we discussed Qlik Sense Mapping Functions. In this Qlik Sense Null Functions tutorial, we will talk about NULL functions which we use to detect and return NULL values, if present in the data record. You were introduced to the concept of NULL values in the QlikView tutorials. So, the null values are such places or cells in a data table or record which contain no data value of any sorts and are thus called NULLs or nothingness. Even though they contain no value, these values are of some significance in Qlik Sense, and for that, the two functions, IsNull() function and NULL() functions.  

So, let’s start Qlik Sense Null Functions Tutorial.

Qlik Sense Null Functions – IsNULL and NULL

2. Qlik Sense Null Functions

i. IsNull() function

The IsNull() function checks the values for Null and returns -1 (True) if it is Null and 0 (False) if it is not Null. This function evaluates an expression logically and answers in -1 or 0.

Have a look at Qlik Sense Scatter Plot

The syntax of Qlik Sense IsNull Function:

IsNull(expression)

For example, we load a table inline called NullsDetect where we have loaded some values in two columns and four rows where some actual data values and some null values have been loaded. Now, the resultant table which will be made will have three columns, for ID, Value and IsItNull which returns T if a value is Null and F if it is not.

NullsDetect:
LOAD *,
If(IsNull(Value), 'Yes', 'No’) as IsItNull;
LOAD * Inline
[ID, Value
0,
1,NULL
2,-
3,Mark];

Resultant table,

So, in the sample code given, the IsNull(Value) function first evaluates the values in the column ‘Value’ to detect Null. And then in the resultant table, we get three Ts because first three values are Null and the last one ‘F’ as Mark is a string value and not a Null value. In the system’s logic the True or T is recorded as -1 and False, or F is returned as 0.

ii. NULL() function

This function returns a null value. It is different from IsNull function as that returns a logical answer in True or False, whereas NULL() function returns ‘-‘ in place of a null value.

Do you know about Qlik Sense Line Chart

The syntax of Qlik Sense Null Functions:

Null()

For instance,

In the script given below, we will use the values in the column ‘Value’ in the table loaded inline which are a mix of nulls and other (numeric or string) values. Using the Null() function, we will see which values are actually null and which are not.

NullsConvert:
LOAD *,
If(len(trim(Value))= 0 or Value='NULL' or Value='-', Null(), Value ) as ValueNullConv;
LOAD * Inline
[ID, Value
0,
1,NULL
2,-
3,29
4,Mark];

Resultant table,

Thus, as you can see in the table, the data values in the field ‘Value’ gets evaluated by the Null() function which returns ‘-‘ if a null is encountered and returns ‘Value’ if a string or numeric value is encountered.

Let’s revise Qlik Sense Conditional Functions

So, this was all in Qlik Sense Null Functions. Hope you like our explanation.

3. Conclusion

Thus, in this Qlik Sense Null Functions article, we learned about how the two null functions deal with null values. As the IsNull() is a function that logically evaluates a set of values and returns -1 or 0 for if a value is null or not. On the other hand, the Null() function detects the null values from a data set and returns ‘-‘ symbol in its place.

Still, if you have any query regarding Qlik Sense Null Functions, ask in the comment tab.

See also – 

Qlik Sense Gauge Chart

Reference for Qlik Sense