Discussion Board for collaboration on QlikView Scripting.
We have a table consisting of:
RowId - a unique identifier for each row
A - any real number
B - any real number
There are a couple of ways to sum A and B together:
Sum(A + B) is equivalent to Sum(Aggr(A + B, RowId))
When A or B can be NULL, Sum(A + B) is not equivalent to Sum(A) + Sum(B).
Why? Because any calculation involving NULL will yield NULL!
To take care of this, you can alter the field on load:
IF(IsNull(A), 0, A) AS A
Would there be any situations in which you need a metric to be NULL? If so, is there a way to treat NULL as 0 in Sum(A + B)?
If you instead of A+B write RangeSum(A,B), you will treat NULL as zeros. Hence
View solution in original post
You can use the NullAsValue function to set how QlikView interprets nulls in the load script.
alt(case1[ , case2 , case3 , ...] , else)
The alt function returns the first of the parameters that has a valid number representation. If no such match is found, the last parameter will be returned. Any number of parameters can be used.
Hi Dirk, If the fields are number (either real or intergers) you can use the ALT function.
Alt(A,0) AS A
Alt(B,0) AS B