NULL Functions

NULL functions are used for identifying or interacting with NULL values.

This article will cover the basic principles of working with these functions in the data load editor and visualisations, including:

  • The general function syntax
  • Working with NULL functions in the data load editor
  • Working with NULL functions in visualisations

For a complete, up-to-date list of NULL functions, please see the Qlik Sense help pages here.

The NULL function syntax

The general function syntax varies depending on the function. In this section, we will use an example function and decipher its syntax. This understanding will then help you read the syntax of other functions.

Example 1: IsNull

The IsNull() function is used to check whether the value is a NULL value. If it is, the function returns true (-1), otherwise, it returns false (0).

Copy code 


IsNull(expr )

The rules for reading the function syntax are as follows:

  1. The function name itself is first and all the parameters you need to enter go between brackets: ().
  2. The various elements you will need to enter are separated by commas. In this function, there is only one element: expr. This is the field or value you want to check for NULLs.
  3. Anything between square brackets [] is an optional parameter. In this function, there are no optional parameters.

An example of this function being used would be:

Copy code 


IsNull("Customer Name")

The resulting field would return -1 where the “Customer Name” values are NULL and 0 where they are not.

How to use NULL functions in the data load editor

Here are some important points on using NULL functions in the data load editor:

  • All of the functions can be used throughout the whole load script, not just within a LOAD or SELECT statement
  • The functions can be nested and combined with other types of functions
  • The functions are particularly useful for cleansing data and for preparing data to be used in visualisations in the most efficient way.

Here is an example of the Null() and IsNull() functions being used:

Copy code 


CUSTOMERS:
LOAD
    "Customer ID",
    "Customer Name",
    "Customer Phone",
    "Customer Address",
    If(IsNull("Customer Name"), 'Unknown', "Customer Name") as "Customer Name Cleaned",
    If("Customer Region" = ' ', Null(), "Customer Region") as "Customer Region"
    
FROM [customers.qvd] (qvd);

The newly created field “Customer Name Cleaned” will contain the value ‘Unknown’ where the field “Customer Name” contains a NULL value. The field “Customer Region” is calculated by checking whether the value is a blank string and if it is, a NULL value is returned, otherwise, the original “Customer Region” is returned. The usefulness of being able to denote dimension values as NULLs is that you can untick the ‘show NULLs’ tickbox in visualisations to ignore these values.

How to use NULL functions in visualisations

At the time of writing, all NULL functions can be used in visualisations in the same way as they are used in the data load editor.

  • The functions can be nested and combined with other types of functions.
  • The functions can be used in dimensions or measures depending on your requirements.

Here is an example of using the Null() function to convert specific strings to NULL to filter them out of the visualisation. The dimension formula used is:

Copy code 


If(WildMatch([Customer Name],'* Ltd*'), [Customer Name], Null())

Note that we have unticked the ‘Include null values’ option in the dimension to remove NULL values from the visualisation.