topic Re: Distinguishing missing values from null values in if clauses in QlikView

Distinguishing missing values from null values in if clauses

Anonymous — Thu, 20 Apr 2017 21:10:39 GMT

Hi folks,

I have the following scenario:

Dimension	Year1	Year2	Year3
Company 1	1,000.00	1,000.00	(division by zero result)
Company 2	2,000.00	1,000.00	Missing value
Company 3	5,000.00	3,000.00	Missing 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

Re: Distinguishing missing values from null values in if clauses

Oleg_Troyansky — Thu, 20 Apr 2017 21:23:18 GMT

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

Re: Distinguishing missing values from null values in if clauses

Anonymous — Thu, 20 Apr 2017 21:33:15 GMT

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

Re: Distinguishing missing values from null values in if clauses

Oleg_Troyansky — Thu, 20 Apr 2017 21:44:16 GMT

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

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

Re: Distinguishing missing values from null values in if clauses

Anonymous — Thu, 20 Apr 2017 21:51:06 GMT

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".

Re: Distinguishing missing values from null values in if clauses

Oleg_Troyansky — Thu, 20 Apr 2017 22:05:37 GMT

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

Re: Distinguishing missing values from null values in if clauses

jpitfield — Fri, 21 Apr 2017 01:24:26 GMT

have you tried:

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

Re: Distinguishing missing values from null values in if clauses

Anonymous — Mon, 24 Apr 2017 15:34:46 GMT

Hi guys!

Still not working!

I I screenshotted my graphic to try explain better:

IsNull(sum(revenue))

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:

Thanks guys.

Rubens

Re: Distinguishing missing values from null values in if clauses

Peter_Cammaert — Mon, 24 Apr 2017 16:11:16 GMT

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...

Re: Distinguishing missing values from null values in if clauses

Anonymous — Mon, 24 Apr 2017 16:47:41 GMT

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

Re: Distinguishing missing values from null values in if clauses

eva_filep — Mon, 24 Apr 2017 18:02:45 GMT

I am working in Qlik Sense not Qlik View, but I guess the logic must be the same.

Your field that stores the following values:

sum(profit) / sum(revenue)

Missing

no Profit

has to be a dimension not a measure since you have text mixed up with numbers.

Therefore sum function cannot be used in your formula.

I got this:

by using the formula:

If(Revenue= 0,'no Revenue',

if(IsNull(Revenue),'Missing', Num( Profit/Revenue, '%#,##0.#', '.' , ',' )))

Maybe you'd need to create Revenue and Profit master items to sum up your original fields(?) and then you can create the dimension

Regards,

Re: Distinguishing missing values from null values in if clauses

Anonymous — Mon, 24 Apr 2017 18:17:46 GMT

I'm just spit balling here, but I think if you include a comparison to a field that is always on your revenue table you can do it. For instance, a row number field. Then you could do something like:

=if(isnull(RevenueRowNoField),'MissingValue',if(sum(Value)=0,'No Revenue',sum(Value)))

Re: Distinguishing missing values from null values in if clauses

Anonymous — Mon, 24 Apr 2017 18:36:53 GMT

I think that is not entirely true. This option is functional on straight tables. But I don't understand at all why the last row will be displayed.

(QV11.2 SR15)

TABLE1:
LOAD MonthName(YearMonth) as YearMonth;
LOAD * INLINE [
YearMonth
01.01.2015
01.02.2015
01.03.2015
01.04.2015
01.05.2015
01.06.2015
01.07.2015
];

TABLE2:
LOAD MonthName(YearMonth) as YearMonth,Value;
LOAD * INLINE [
YearMonth, Value
05.01.2015, 15
03.04.2015, 25
01.06.2015, 35
];

Re: Distinguishing missing values from null values in if clauses

Peter_Cammaert — Tue, 25 Apr 2017 10:07:01 GMT

Indeed, you get a change in your output table. But is this really related to a "Missing" value. Seems more like activating the "Show All Values" setting in the Dimensions tab.

And in which field did you change the presentation value? Null or Missing? This looks exactly like what Oleg already said.

I'm beginning to think that QlikView considers missing in a Straight table as undecidable. How should a QlikView straight table know that something should be there when there is no data? (Almost) every aggregation function will return something, at least a 0 value which is fundamentally different from Null or missing. And NULL already is equivalent to No Value, No Data, "Nothing to see here"...

Good subject for elaborate discussion

Re: Distinguishing missing values from null values in if clauses

Anonymous — Tue, 25 Apr 2017 17:21:38 GMT

I haven't activated "show all values" for the dimension as you can see in my attachment...