topic Re: Handling null values in expression in QlikView

Handling null values in expression

Anonymous — Wed, 11 Apr 2018 11:46:46 GMT

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

Re: Handling null values in expression

vvira1316 — Wed, 11 Apr 2018 12:11:14 GMT

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

Re: Handling null values in expression

sunny_talwar — Wed, 11 Apr 2018 12:29:51 GMT

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

Re: Handling null values in expression

Anonymous — Wed, 11 Apr 2018 14:34:27 GMT

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.

Re: Handling null values in expression

Anonymous — Thu, 12 Apr 2018 06:27:26 GMT

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

Re: Handling null values in expression

vvira1316 — Thu, 12 Apr 2018 11:51:25 GMT

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.