topic Re: Substitute Zero for NULL Value in QlikView

Substitute Zero for NULL Value

Wed, 25 Jan 2012 00:11:27 GMT

I just can't make heads or tails of Qlikview, nothing ever seems to make sense in this tool.

I have a field which may contain NULL values. They display in my pivot table as a "-" which I do not want. In SSRS or any other tool I can write an expression to handle this and change the "-" to 0. How do I do this in Qlikview?

Right now i'm doing the following:

IF(IsNull(SUM(diff_clicks)),0,SUM(diff_clicks))

But this does not work, I still get the "-" in the fields where there is no number.

I need someone, in plain English and easy to follow steps, to explain to me how to do this.

Substitute Zero for NULL Value

disqr_rm — Wed, 25 Jan 2012 00:46:42 GMT

Try

NumSum(SUM(diff_clicks))

NumSum will return 0 for a null value.

Substitute Zero for NULL Value

Wed, 25 Jan 2012 02:03:35 GMT

Try Rangesum(SUM(diff_clicks)). The NumSum function is now obsolete.

Substitute Zero for NULL Value

Anonymous — Wed, 25 Jan 2012 05:30:46 GMT

I might be wrong, but as fare as i remember there is at property in the pivot table of how null values are displayed.

Henrik

Re: Substitute Zero for NULL Value

Wed, 25 Jan 2012 07:09:03 GMT

Hi!

Go to Presentation Tab:

See the red Circle in Attached File.

Write 0 instead of '-' .

Substitute Zero for NULL Value

jagan — Wed, 25 Jan 2012 08:02:11 GMT

Hi,

Try using the Alt method like below

=Alt(SUM(diff_clicks), 0)

Regards,

Jagan.

Substitute Zero for NULL Value

SunilChauhan — Wed, 25 Jan 2012 09:01:41 GMT

lF(IsNull(SUM(diff_clicks)),0,SUM(diff_clicks))

lF(len(SUM(diff_clicks)),0,SUM(diff_clicks))

lF((SUM(diff_clicks))=' ',0,SUM(diff_clicks))

hope this helps

Re: Substitute Zero for NULL Value

Anonymous — Fri, 31 Jan 2014 18:57:47 GMT

Lots of good stuff here, but if you have no data for a particular cell in a straight or pivot table, for example, none of these expressions will even be executed.

You can take advantage of QlikView's loosely typed nature and the only operator that doesn't return null when one of its operands is null: &.

For example, 0+NULL = NULL, while 0&NULL = 0. It's also important to note that 0&0 = 00.

So, something like num(0&sum(Sales),'0') can make a value appear where there was none before. You may also need set analysis with {1} to ensure all possible dimension values appear.

Hope this helps!

Re: Substitute Zero for NULL Value

srinivasa1 — Fri, 31 Jan 2014 19:14:13 GMT

In Dimensions clik on "suppress when value is Null".it may help