We use different techniques to test for Null so that such values can be dealt with properly and ensure a proper functioning of the application. Qlikview has its own method of handling Nulls and is somewhat different to the way in which we normally handle Nulls in web applications.
If you have a list of values in a listbox, all the null values will be hidden (or supressed). That is because Qlikview goes by the logic null = 'nothing', which then put in to context throws the question of 'how to select nothing?'
The solution to this is to define a default value for nulls in the Qlikview script. This can be done using the NullAsValue function.
Take a look at the screenshot below, I've loaded a list of student records which intentionally have some values removed for illustration purposes:
The table box contains 2 records which has nulls. Suppose I select one of the 2 values in the title list box, would I be able to see record number 6? Answer is no. If we had authority to update the information in the data source then that's fine. However, if not, we cannot afford to risk the inconsistencies that would arise in the application as a result of the nulls.
This is where the NullAsValue function fits in. There are two parts for this solution.
1. Define the default variable which will then be used by the NullAsValue function when nulls are encountered.
2. Identify which fields need to be set to the default value.
Let's look at the step 1:
* Go to the script editor, at the top, define the default variable
* I've set the default value to <Undefined> to be displayed when a null is encountered.
Step 2:
* Define the NullAsValue function at the top and specify the fields to be set with the default values
* For this example, I've only set the 'Title' field, but depending on your requirement you could specify more than one field separating each with a comma or '*' if you'd like to eliminate nulls from the dataset.
Now reload the data. Once complete you will notice the default value appearing in place of the null in the table box and the list box.
You can now select the record ID 6, which previously was not selectable.
