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

[agaetisproject]

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.

[Oleg_Troyansky]

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

[nagaiank]

You may also use

Alt([Risk factor],0) as RiskFactor