Alternative to Null

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