Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Highlighted
agaetisproject
New Contributor III

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

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.

Tags (2)
2 Replies
MVP & Luminary
MVP & Luminary

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

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
Valued Contributor III

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

You may also use

Alt([Risk factor],0) as RiskFactor