How to Handle Null Values

De Qknow
Ir para: navegação, pesquisa

Introduction

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.

Kleenean 1.PNG

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

Null Values 1.PNG

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 =;

Remember!

  • 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 Chr(32).
  • 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 Chr(09).

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).

Replacing Nulls

Null Values 2.PNG

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.

After use 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.

  • NullAsValue can be used more than a time in different places along the script. Depends of your intention.
  • Redefine NullValue variable how many times is needed along the script.
  • Use asterisk (*) in NULLASVALUE if 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 NULL() and IsNULL() functions.
  • Remember NULL can be resulted from JOIN and CONCATENATE.



More Posts



Main Page