When no data can be produced for a certain field as a result of a database query and/or a join between tables, the result is normally a NULL value.
Overview
The Qlik Sense logic treats the following as real NULL values:
-
NULL values returned from an ODBC connection.
-
NULL values created as a result of a forced concatenation of tables in the data load script.
-
NULL values created as a result of a join made in the data load script
-
NULL values created as a result of the generation of field value combinations to be displayed in a table
For more information about NullAsValue, see NullAsValue.
Text files per definition cannot contain NULL values.
Associating/selecting NULL values from ODBC
It is possible to associate and/or select NULL values from an ODBC data source. For this purpose a script variable has been defined. The following syntax can be used:
The symbol <sym> will substitute all NULL values from the ODBC data source on the lowest level of data input. <sym> may be any string.
In order to reset this functionality to the default interpretation, use the following syntax:
SET NULLDISPLAY=;
If you wish to have the Qlik Sense logic interpret NULL values returned from an ODBC connection as an empty string, add the following to your script before any SELECT statement:
SET NULLDISPLAY=";
For more information about NullDisplay, see NullDisplay .
Creating NULL values from text files
It is possible to define a symbol, which when it occurs in a text file or an inline clause will be interpreted as a real NULL value. Use the following statement:
The symbol <sym> is to be interpreted as NULL. <sym> may be any string.
In order to reset this functionality to the default interpretation, use:
SET NULLINTERPRET=;
For more information about NullInterpret, see NullInterpret .
Propagation of NULL values in expressions
NULL values will propagate through an expression according to a few logical and quite reasonable rules.
Functions
The general rule is that functions return NULL when the parameters fall outside the range for which the function is defined.
| Expression | Result |
|---|---|
| asin(2) | returns NULL |
| log(-5) | returns NULL |
| round(A,0) | returns NULL |
As a result of the above follows that functions generally return NULL when any of the parameters necessary for the evaluation are NULL.
| Expression | Result |
|---|---|
| sin(NULL) | returns NULL |
| chr(NULL) | returns NULL |
| if(NULL, A, B) | returns B |
| if(True, NULL, A) | returns NULL |
| if(True, A, NULL) | returns A |
The exception to the second rule are logical functions testing for type.
| Expression | Result |
|---|---|
| isnull(NULL) | returns True (-1) |
| isnum(NULL) | returns False (0) |
Arithmetic and string operators
If NULL is encountered on any side of these operators NULL is returned.
| Expression | Result |
|---|---|
| A + NULL | returns NULL |
| A - NULL | returns NULL |
| A / NULL | returns NULL |
| A * NULL | returns NULL |
| NULL / A | returns NULL |
| 0 / NULL | returns NULL |
| 0 * NULL | returns NULL |
| A & NULL | returns A |
Relational operators
If NULL is encountered on any side of relational operators special rules apply.
| Expression | Result |
|---|---|
| NULL (any relation operator) NULL | returns NULL |
| A <> NULL | returns True (-1) |
| A < NULL | returns False (0) |
| A <= NULL | returns False (0) |
| A = NULL | returns False (0) |
| A >= NULL | returns False (0) |
| A > NULL | returns False (0) |