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.
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.
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:
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:
For more information about NullDisplay, see NullDisplay .
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:
For more information about NullInterpret, see NullInterpret .
NULL values will propagate through an expression according to a few logical and quite reasonable rules.
The general rule is that functions return NULL when the parameters fall outside the range for which the function is defined.
As a result of the above follows that functions generally return NULL when any of the parameters necessary for the evaluation are 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.
|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.
|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|
If NULL is encountered on any side of relational operators special rules apply.
|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)|