topic Re: Null appears in chart Calculated Dimension in spite of IsNull test in QlikView

Null appears in chart Calculated Dimension in spite of IsNull test

Anonymous — Wed, 25 Nov 2020 16:16:04 GMT

I have created a chart in QlikView in which I am using a Calculated Dimension that uses the following expression.

=if(IsNull([GCK HQ-System]),'zzNull',if(Trim([GCK HQ-System])='','zzBlank',[GCK HQ-System]))

Now, in spite of using the IsNull() function in the IF() statement, the expression itself returns a NULL value. The NULL value is displayed as whatever string value that I enter in the Chart Properties | Presentation | Null Symbol box.

It seems that IsNull() on the "GCK" field name never gives a positive test. But nevertheless the overall expression itself is generating an exception that causes a NULL value to be output and displayed as the "Null Symbol" string.

What is going on? And how can I fix it?

Re: Null appears in chart Calculated Dimension in spite of IsNull test

sunny_talwar — Wed, 06 Apr 2016 15:34:19 GMT

By using a calculated dimension you won't be able to change anything for the expression. To fix the expression, you might need to do a similar InNull() test on the expression. Note: IsNull() won't work for Missing Value (NULL handling in QlikView)

Re: Null appears in chart Calculated Dimension in spite of IsNull test

engishfaque — Wed, 06 Apr 2016 18:03:29 GMT

Dear Andrew,

Here is a script,

If(Len(Trim([GCK HQ-System])) = 0, 'zzNull', If(Len(Trim([GCK HQ-System])) > 0, [GCK HQ-System], 'zzBlank'))

Kind regards,

Ishfaque Ahmed

Re: Null appears in chart Calculated Dimension in spite of IsNull test

Anonymous — Wed, 06 Apr 2016 18:08:51 GMT

sometimes in qlikview if there is a null value it might not treat it as null.. using the same condition check for len([GCK HQ-System)=0

in order to understand more about your data do this in a script and create one more field called len([GCK HQ-System) put that new field in list box and check how the nulls are treated. Hope that helps.

Re: Null appears in chart Calculated Dimension in spite of IsNull test

MarcoWedel — Wed, 06 Apr 2016 23:13:19 GMT

can you provide a sample application to demonstrate a solution?

thanks

regards

Marco

Re: Null appears in chart Calculated Dimension in spite of IsNull test

Anonymous — Thu, 07 Apr 2016 08:10:37 GMT

Here is a screen shot of the table, with the revised formulae suggested. As you can see, both the if(Len(Trim statement and the Len() statement return a Null which is displayed by the text that I put in the Table | Properties | Presentation | Null Symbol field..

Re: Null appears in chart Calculated Dimension in spite of IsNull test

Anonymous — Thu, 07 Apr 2016 08:15:12 GMT

And here BTW is a screenshot showing that the test IsNull() does itself return a Null value instead of True or False as one would expect..

Re: Null appears in chart Calculated Dimension in spite of IsNull test

swuehl — Thu, 07 Apr 2016 13:53:31 GMT

Hi Andrew,

this looks indeed strange.

Could you maybe comment on

- Which QV version are you using (and I assume you are referring to the desktop client)?

- In above screenshots, you are using a plain straight table with the given dimension & expression, not other settings changed (except 'Null symbol' on presentation tab)? No hidden expressions / attribute expressions, maybe using set analysis?

- Do you see this issue also when using other fields?

- If not, is there anything special about [GCK HQ-System]?

- Is it a key field?

- I assume the line will disappear when using 'Suppress when Value is NULL' on dimension tab (you are just not using it for demonstration of the issue itself)?

- Have you tried isolating the issue by loading only this field and trying to reproduce the issue with your chart?

- Would it then be feasible that you share this sample QVW (attaching it to your post)?

Regards,

Stefan