Wednesday, 4 April 2012

Handling Nulls in QlikView

The way in which QlikView's associative engine works means Nulls don't really exist in the data it holds, a Null is simply an absence of a data item. This can make working with Nulls a little tricky until you understand the tricks and tips which can make it a little simpler. For example, what if I want to display customers with missing phone numbers, I can't simply make a selection for Null in the phone number list box as it doesn't exist as a data item.

Here are a few tips and tricks which can make working with Nulls in QlikView a little simpler.

The Null() and IsNull() Functions

It is possible to generate a Null value in the script or an expression using the null() function. For example, you might want to replace zero's with a Null so that they don't take up valuable resources when dealing with very large data sets or don't effect an average calculation. This is pretty simple using the null() function like this:

if(SalesValue=0, null(), SalesValue) AS SalesValue

With this in mind you might think you could also use the null() function to test if a field contains Null like this:

if(SalesValue=null(), 'NULL', SalesValue) AS SalesVale

Great, I can now select the Nulls right? Wrong! It is not possible to compare against the null() function as shown above and interesting doing so will not result in a script error either. Instead a separate isnull() function exists for this purpose and can be used like this:

if(isnull(SalesValue), 'NULL', SalesValue) AS SalesValue

The NullAsValue Statement

The above example of replacing Nulls is often the first solution people reach for when building a QV app. However there is an alternative which can not only be cleaner but also a more efficient solution. The NullAsValue statement does as you might expect it to, replacing Nulls in the given list of fields with a value which can be seen and selected in a list box. The syntax is very simple as follows:

NullAsValue Field1, Field2;

As you can see, it is possible to list more than one field and it will also except wildcards in a similar way to the Qualify statement as described in a previous post here. By default, NullAsValue replaces the Null with a zero length string but this can be changed by setting the NullValue variable as follows:

SET NullValue = '<Null>';
NullAsValue SalesValue;
NullAsNull *;


There is one thing to be careful of when using NullAsValue though. Optimised QVD loads do not reprocess the data within the QVD row by row and thus it will not apply the NullValue to the data. To get around this you must force an unoptimised load of the QVD. This can be done by adding a WHERE clause to the load that will always apply like this:

LOAD
    *
FROM myqvdfile.qvd (qvd)
WHERE 1=1;

Nulls in Basic Maths

Another issue with Nulls occurs when you want try to perform simple maths with them. For example, you might expect that 5 plus Null is 5 when the real answer is in fact Null. This can be a very real problem when adding 2 fields together and one or both have records containing Nulls. As always there are multiple solutions.

One method is to use the alt() function which accepts a list of values and returns the first which is a true numerical value. We can thus use it as follows to solve the problem of performing maths around Nulls:

alt(SalesValue,0) + alt(SalesMargin,0) AS GrossSalesValue

If either SalesValue or SalesMargin contain a Null value, it is instead treated as zero and the calculation will give the result we might have expected instead.

An alternative is to use the range functions such as rangesum(), rangecount() and rangeavg(). These functions are passed one or more values and return the sum, count or average respectively of only those passed values that are truly numerical. Nulls are not considered numerical values and thus are ignored and so we can can use rangesum() to solve the same addition problem as follows:

rangesum(SalesValue, SalesMargin) AS GrossSalesValue

Counting Nulls

It can often be necessary to count Nulls. For example I might want to know find out how many customers I have no address for. Counting Nulls in QlikView is made easy using the nullcount() function which pretty much does exactly as you'd expect. It can be used in both the script and expressions and the syntax is very straightforward:

=nullcount(Address)

Selecting Nulls (including in set analysis)

You obviously can't directly select Null in a list box as it won't appear although you could use one of the methods describe above, such as NullAsValue, to convert the Nulls into a value that can be selected. You might think searching for "=null()" might work bit it won't. However it is possible to indirectly select Nulls within list boxes or even set analysis expressions in QlikView using an advanced search. You can't search within the same field as it won't possible to select the Nulls when you apply the search but you can search in one field for those values that relate to where another field is Null.

Using the example we considered above of finding my customers for whom I have no address, we can't search for null() in the Address field but we can search the Customer field for isnull(Address) like this:


Advanced searches can be used in set analysis expressions as well as list boxes and so we could use the following expression in a chart or text object too:

count({$<Customer={"=isnull(Address)"}>} Customer)

I'll leave you with one last thing to keep in mind with Nulls and set analysis. One thing that sometimes people don't fully understand is that there is a big difference between selecting all values in a field and selecting no value. The difference occurs because of Nulls. Selecting all values in a field excludes records for which that field contains a Null value. You can do just this in set analysis like this:

count({$<Address={"*"}>} Customer)

And a quick word of warning. You may see the following syntax to exclude Nulls written sometimes on blogs and forums:

count({$<Address-={"null()"}>} Customer)

If you try it you'll find it may well work, but it is considered bad syntax and QlikTech may correct it in the future and so it is best avoided to ensure your application is future proof.

3 comments:

  1. as always, really good articles... thank you very much!

    ReplyDelete
  2. Thanks a lot for this very clear article. I wanted to understand how nulls are handled in QV and your post made it absolutely clear. Thanks a lot for sharing your knowledge

    ReplyDelete