Wednesday, 11 May 2011

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

2 comments:

  1. Hi, but also you can use
    rangesum(A,B) so the non-numerical values will be treated like zero

    Rgds

    Héctor Muñoz.

    ReplyDelete
  2. Hi Hector,

    Thanks for the contribution.

    Just note that this is not "but also you can use...", it is "and also you can use..."

    My purpose here is to tell people about Alt.

    Regards,

    Stephen

    ReplyDelete