topic Re: Replace null value (or missing value) in source Excel value by 0 in script in QlikView

Replace null value (or missing value) in source Excel value by 0 in script

agaetisproject — Sun, 12 Jun 2016 19:44:09 GMT

Why is this syntax not working?

if(isnull("Risk factor"), '0.0',"Risk factor") as RiskFactor

It seems it delivers a 0 in Excel but that the zero is not well interpreted as my computation using the RiskFactor are not happening.

Re: Replace null value (or missing value) in source Excel value by 0 in script

Oleg_Troyansky — Sun, 12 Jun 2016 20:44:49 GMT

I found IsNull() to be flaky and return unexpected results sometimes. Instead, I'm using the following formula that also covers an empty string or any number of spaces instead of the value:

if(len(trim("Risk factor"))) = 0, 0 ,"Risk factor") as RiskFactor

Another possible way to convert nulls and zeros into zeros is to use the RangeSum() function :

RangeSum("Risk factor") as RiskFactor

Any nulls will get converted into zeros.

cheers,

Oleg Troyansky

Check out my new book QlikView Your Business - The Expert Guide for QlikView and Qlik Sense

Re: Replace null value (or missing value) in source Excel value by 0 in script

nagaiank — Sun, 12 Jun 2016 21:50:20 GMT

You may also use

Alt([Risk factor],0) as RiskFactor