There are lots of Null values floating around in data and QlikView has some interesting ways to deal with it.
For example, we can turn on the NullAsValue option for a field that we are going to load:
NullAsValue A, B;
Any values that were previously Null (and do not display in a list box) will now display as blank strings.
There is a small problem when I want to do any maths on a field that might have a Null value and that is that any number plus Null is Null. A great way to deal with this is, if I expect there might be Null values in a field, to use the Alt() function.
Alt accepts a list of values and will return the first of those values that has a valid number. So, instead of:
Load A+B as AB, ...
I can have:
Load Alt(A,0)+Alt(B,0) As AB, ...
Now, if either A or B have a Null value, it is treated as zero and my calculation can continue and give a good result.
Stephen Redmond is CTO of CapricornVentis a QlikView Elite Partner