Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Highlighted
rrodriguesrr
New Contributor III

Distinguishing missing values from null values in if clauses

Hi folks,

I have the following scenario:

DimensionYear1Year2Year3
Company 11,000.001,000.00(division by zero result)
Company 22,000.001,000.00Missing value
Company 35,000.003,000.00Missing Value

What I want is to replace null values by 'no Revenue', but nothing has to happen with missing values columns.

I've tried:

Alt(

     sum(profit) / sum(revenue), 'no Revenue')

Also:

Alt(

     If(IsNull(sum(revenue) = 0,

          sum(profit) / sum(revenue), 0), 'no Revenue')

However, the formula above also replaces missing values by 'no Revenue'.

Could you help me with this?

Thanks,

Rubens Rodrigues

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

Re: Distinguishing missing values from null values in if clauses

In QlikView charts, you can substitute both Missing and Null values with two separate symbols - wouldn't that help? The settings are at the bottom of the Presentation tab.

Cheers,

Oleg Troyansky

Upgrade your Qlik skills with my book QlikView Your Business: An Expert Guide to Business Discovery with QlikView and Qlik Sense

rrodriguesrr
New Contributor III

Re: Distinguishing missing values from null values in if clauses

It doesn't fit in my case, because what I'm really trying to do is to treat division by zero.

As far as I understood, Qlikview is considering the division by zero result as "missing value".

I want something similar to the Excel function IFERROR.

I've tried to force null values in cells which division by zero results were expected and it woked. However, when I use the Alt function, it replaces both null and missing values by whatever I set.

Very confuse.

Thanks

MVP & Luminary
MVP & Luminary

Re: Distinguishing missing values from null values in if clauses

In my testing, division by zero is treated as NULL, not as MISSING.

The Alt() function returns the first non-NULL numeric value.

rrodriguesrr
New Contributor III

Re: Distinguishing missing values from null values in if clauses

If I just enter with the divivion by zero formula and go to the chart properties and substitute the value for Null, nothing happens. Howver, it works if I substitute the value for missing. This is the reason I thought Qlikview considered the division by zero results as "missing values".

MVP & Luminary
MVP & Luminary

Re: Distinguishing missing values from null values in if clauses

I tried to generate NULL and MISSING values, including division by zero, and everything appears like NULL - even when the value is actually missing.

jpitfield
New Contributor II

Re: Distinguishing missing values from null values in if clauses

have you tried:

If(IsNull(sum(revenue)) = 0,sum(profit) / sum(revenue),if(IsNull(sum(revenue)) = -1, 'no Revenue', sum(profit) / sum(revenue)))

rrodriguesrr
New Contributor III

Re: Distinguishing missing values from null values in if clauses

Hi guys!

Still not working!

I I screenshotted my graphic to try explain better:

  • IsNull(sum(revenue))

Image1.png

  • If(IsNull(sum(revenue)) = 0, 'NULL')
    • I was expecting only the highlighted informatin above to be replaced by NULL, but it's not what happen:

Image2.png

Thanks guys.

Rubens

Re: Distinguishing missing values from null values in if clauses

As an aside: Suppress Missing isn't working because "this option is not functional on Straight tables" (QV Desktop Help). Which may mean that Missing values are altogether alien to straight tables.

Weird logic...

rrodriguesrr
New Contributor III

Re: Distinguishing missing values from null values in if clauses

Here I1m just testing NULL and MISSING. It's not the real logic.