topic Re: How can I make null equal to zero in an expression? in QlikView

How can I make null equal to zero in an expression?

Anonymous — Sun, 21 Dec 2014 10:42:46 GMT

I am relatively new to QlikView and I am making a practice QV document using the Northwind Database. I am trying to write an expression for [Total Inventory]. The expression itself I think is a simple expression.

Sum ([Inventory Purchased.Quantity Purchased] - [Inventory Sold.Quantity Sold])

The problem I am having however is that in the [Inventory Sold.Quantity Sold] They did not use zero's for zero quantity and instead the field is null.

Is there a way to make null equal to zero so the expression will return the desired calculation?

Re: How can I make null equal to zero in an expression?

mdmukramali — Sun, 21 Dec 2014 10:52:35 GMT

Dear Chris,

Can you provide some sample data so that we can help you i better way.

and I think your formula should be like

Sum ([Inventory Purchased.Quantity Purchased]) -Sum([Inventory Sold.Quantity Sold])

Thanks,

Mukram

Re: How can I make null equal to zero in an expression?

Anonymous — Sun, 21 Dec 2014 10:53:27 GMT

Hi,

try in script:

Load

if(isnull(Field),0,Field) as Field,

From xyz;

Regards

Neetha

Re: How can I make null equal to zero in an expression?

Anonymous — Sun, 21 Dec 2014 10:59:40 GMT

I think I answered my own question.

I changed my expression to the following:

Sum (Alt([Inventory Purchased.Quantity Purchased],0) - Alt([Inventory Sold.Quantity Sold],0))

It seems to have done the trick!

Thanks for those who replied!

Re: How can I make null equal to zero in an expression?

Sun, 21 Dec 2014 11:01:23 GMT

try this expression in back end

if(IsNull( [Inventory Sold.Quantity Sold])=-1,0, [Inventory Sold.Quantity Sold]) as [Inventory Sold.Quantity Sold]

hope this helps you.

Re: How can I make null equal to zero in an expression?

rajeshvaswani77 — Sun, 21 Dec 2014 11:02:26 GMT

Hi Chris,

Instead of Sum you can use Rangesum. From help below is the text that explains rangesum.

rangesum(expr1 [ , expr2, ... exprN ]) Returns the sum of a range of 1 to N arguments. As opposed to the + operator, rangesum will treat all non-numeric values as 0. The argument expressions of this function may contain Chart Inter Record Functions with a third optional parameter, which in themselves return a range of values. Examples: rangesum (1,2,4) returns 7 rangesum (1,'xyz') returns 1 rangesum (null( )) returns 0 rangesum (above(count(x),-1,3)) returns the sum of the three results of the count(x) function evaluated on the row below the current row, the current row and the row above the current row.

All nulls will be treated as zero.

thanks,

Rajesh Vaswani

Re: How can I make null equal to zero in an expression?

Anonymous — Sun, 21 Dec 2014 11:15:08 GMT

So being new to QlikView, is there a correct way or a best practices way of handling this? I see many solutions to this question!

Re: How can I make null equal to zero in an expression?

Anonymous — Sun, 21 Dec 2014 11:34:07 GMT

Hi,

Try all solutions and compare your self the outputs.

decide the good one.

better solution is handling any transformations in Script.