Handling null values in expression

[Anonymous]

Hello All

I have below expression in pivot table where in I get '-'   ie Null  in few records. Instead of null I want to put '0' .

So that when I calculate Final column which is addition of 3 columns it displays correct figure.

If one of the 3 columns is null then Final column also shows null and does not calculate the remaining two columns sum.

Below is the expression:

if([Amount (ABC)]>'0',

   if(CATEGORY='Specialist' and [IT PV]>='1.90' and [IT PV]<'2.10' ,'300',

     if(CATEGORY='Super Specialist' and [IT PV]>='2.10' and [IT APV]<'2.30' ,'500',

        if(CATEGORY='Super Specialist' and [IT PV]>='2.30' ,'700',0)))) 

Kindly help to get 0 instead of null in above expression.

Thanks & Regards

Chintan Gala

[vvira1316 · Accepted solution]

Hi,

In your script you can check each of those data point for null using IsNull() function and substitute 0 for Null value.

Alternatively in chart try putting 0 for Null Symbol. Not sure if this will help but I'm sure script check above should help you.

BR,

Vijay

[sunny_talwar]

I am not sure if the issue is null or missing values... if the problem is missing values, then you might have to generate those in the script

Generating Missing Data In QlikView

[Anonymous]

For some cases we get'0' whereas for some we are getting'-' .

Instead of'-'  we need to show'0'.

As we are adding 3 columns to get final result.

If any of the column has '-' then final column also show'-' and not sum of other two columns.

[Anonymous]

The first part is working perfectly fine.

Second part did not work , it just substitutes null '-' with 0 as a symbol and not numeral '0' which we need.

Thanks for your quick help!

Regards

Chintan

[vvira1316]

That may happen in Qlik when a data point has mix of text/char and numbers it will consider that as string rather than numeric. You could use Num function for that. Glad that you were able to get it working.