How to Handle Null Values
Null values can be gotten when reading a table from a database where a field is defined to accept null values. Null is unknown value, or undefined result. Other way to get null is from reading data from Excel worksheet where formulas result in null values. Typically text files cannot store null values. because always a null value is stored at flat text file, it's replaced by empty. That's right. Empty is not null. Empty is a value defined as not existing. While null is unknown value, empty is known as nothing. Technically, null is different from empty value. Empty is represented by '' (two simple quotes together) or, in some systems "" (two double quotes together). But, null has no representation, although it exists.
Generally, when checking a Null value against any other real values (real values means no nulls) result is a null. For example, if null = '' in most of database systems, result is null. In same way, when checking if NULL = NULL result is NULL, while someone can expect TRUE as result. Any value compared with null, will get a null as result. Typically, null is result from a ODBC/OLEDB connection, Excel file or INLINE forced. In QlikView and QlikSense, nulls are handled with three different results, or Three Value Logic. Remember that nulls can be gotten from JOINS instructions, while one of side can not fill with all values from first side.
In real world, when a null values is gotten a dash is shown in most QlikView/QlikSense components (charts, tables, lists, etc.). When null are shown in a table, for example, no selection can be done. Therefore, if a user click over a null cell no filter is applied. As we said, in QlikView/QlikSense null values are handled using 3 results, not only TRUE or FALSE. In fact, is Kleenex concept in place.
Note: Is not correct say "null values", because null is not a value itself. But, it's a very common and accepted mistake.
Replacing Null on Database Tables
QlikView/Sense has a lot of system variables to handle a lot of situations. Null is not different. To replace any NULL values from tables while script is running, just set what you want to show instead of NULL. This technique will work fine with any tables from databases. Take a look at the picture at side while user is trying to select cells with null values, represented by dash. No filter is applied.
If you want let user select this kind of cells, is necessary replace nulls by something else. For example, is possible to inform user that cells has no values defined, just change it's content by any text, like undefined, or not filled. Of course you can use
IF function in conjunction with
NULL() for each field to change it's content. But is a hard way to handle a lot of fields that can have nulls. What's why a simple variable is available.
If script uses
NullDisplay variable is possible inform to Qlik that replace any NULL from any field by a text defined at script level. The following example will teaches QlikView to replace any occurrence of null by a text.
SET NullDisplay = 'Not Filled';
Using this technique user will be able to select values in tables or charts, as a common dimension value. This variable can be changed along of script execution. So, before load a specific table, just set how you would like handle NULLS from that point of execution. For example, if empty is your choice to replace nulls, just set variable to two single quotes together. If you use a space between single quotes, than you will get a space instead of empty.
SET NullDisplay = '';
If for any reason you need set variable back to default behavior, follow the format below. This means null will be just null.This means null will be just null.
SET NullDisplay =;
- NULL cannot be selected as a filter. Dash in table's cells will be not selectable.
- NULL values in fields cannot be used to link tables between associative model.
- NULL will not participate in table's keys.
- As database's tables, NULL has no representation, neither text or numeric.
- Operations with NULL in QlikView/QlikSense will result in NULL. But, not always.
Handling NULL in Flat File
As the introduction said, NULL cannot be represented in text files, while can be present in Excel or INLINE command. So, maybe your script need handle unexpected characters as NULL instead of real values. But first, is important highlight some aspects as:
- NULL isn't a value. So, two single quotes together is empty, not NULL.
- In QlikView or QlikSense, double quotes is a delimiter for fields, not for strings.
- Two double quotes together can be handled as string for some files or systems.
- Single quotes with a blank space inside is a blank character, not NULL and not empty. That mean
- Blank can be hard blanks. So, it's
Chr(160). This is a blank character (not space), and not null.
- Sometimes tabulations are read as blank, but they are
If you need interpret some differents values as NULL from flat files, it's possible set a new variable for that. The
NullInterpret is a variable that tells to Qlik read some data fields as NULL instead of values found. For example, a field can have dirty data and this can be read as NULL, so just set variable to interpret values as you want. The following example shows how to interpret empty values in field as NULL. Remember, this will work just for files, not to tables from databases.
SET NullInterpret = ;
It's important to say that previous instruction will work fine with text files, but not for Excel with columns with empty values (two single quotes together). For Excel spreadsheets is necessary use two single quotes with no space, like the following set.
SET NullInterpret = '';
Furthermore, if you need change some dirty data by NULL, specify it as input of variable. The following example shows how to get NULL when a unexpected text is found in any field. In this case, every time 'xxx' is found, field's content is replaced by NULL
SET NullInterpret = 'xxx' ;
Another aspect about blank values is important to say. Blanks (spaces) are Chr(32) characters, and will work fine when TRIM function is used to remove blanks from text strings. But, Chr(32) is not alone while talking about blank texts. Also exists Chr(160) called hard blank and, for that, TRIM will not work. Try work with REPLACE and CHR functions when you need handle blanks from Chr(160).
Has we said, if columns content is NULL and you need show any else data instead of a dash, is possible set
Null Display to handle this situation. However, this will apply for all data and columns that will load data after script run this command. Sometimes QlikView's application needs to handle just specific columns, not all. For that, QlikView supports the
NULLASVALUEinstruction. It's used to specify which columns will handle NULL with different values.
For example, the Customer table has NULL entries in State field. As we know, NULL is not selectable and can't be filtered in tables objects or charts. But, if the script replace NULL by some text will let user select all of records with State not filled. At same time, you don't want this behavior for all fields, so using NullDisplay will change everything, including what you don't want to change. That's why
NULLASVALUE is available, because a list of fields can be specified.
NULLASVALUE to specify which fields you wanna change it's NULL content by text, it's time to set a variable that will work with this command. Using
NullValue let you combine what's text will be displayed to fields specified by
NULLASVALUE statement. The following commands will set State's field (Estado) with a different content always a NULL is found for this individual field. Take a look at the picture at side where a table object was used to show some fields, including State (Estado). Take a look at the following commands at script level.
NULLASVALUE Estado; // 'Estado' means State SET NullValue = 'Indisponível'; // 'Indisponível' means Not Available
Other fields were not affected by this automatic replacement. To complete this post some additional informations about NULL.
NullAsValuecan be used more than a time in different places along the script. Depends of your intention.
NullValuevariable how many times is needed along the script.
- Use asterisk (*) in
NULLASVALUEif you wanna apply to all fields. Or, specify many fields separately by commas.
- When using IF function, take in mind NULL will be gotten if no false argument is in place. Example:
=IF(field = 'Best', 'QlikView')
- False argument will be executed in IF function when evaluate results is NULL. For example:
=IF(NULL() = NULL(), 'true', 'false') // This command has no sense, it's used just for teach this concept.
- Other ways to handle NULL is using either
- Remember NULL can be resulted from JOIN and CONCATENATE.
- Using Partial Reload
- Restricting Data for User Login (Section Access)
- How to Use Variables
- Incremental Load Routine
- How to Configure a Multi Language Dashboard
- Transforming Images in Charts
- Highlighting bars on Chart