12 Replies Latest reply: Nov 21, 2013 6:25 AM by Mina Patel

# How can I incorporate null values as zero in a calculation?

I have a calculation  a- b but sometimes a or be may be null, how can I script this?

my code looks something like this at the moment:-

join(Temp table)

*,
(
A) - (B) as Diff;

• ###### Re: How can I incorporate null values as zero in a calculation?

Hi Mena,

Correct me if am wrong, what I get from your question above is that sometimes the value of your A/B is equivalent to null therefore you would like to replace it with a zero?

Regards,

Ram

• ###### Re: How can I incorporate null values as zero in a calculation?

Hi,

maybe with

join(Temp table)

*,
if(isnull(A), 0, (
A)) - if(isnull(B), 0, (B)) as Diff;

or

join(Temp table)

*,
if(len(A)>0, (
A), 0) - if(len(B)>0, (B), 0) as Diff;

• ###### Re: How can I incorporate null values as zero in a calculation?

yes I have outer joined two tables therefore I may have one value where A may be null or vice versa, I want to say if this is the case tehn convert the value to zero.

Sorry for not being clear.

Mina

• ###### Re: How can I incorporate null values as zero in a calculation?

Hi,

Try using Alt() like this

*,
Alt(
A, 0) - Alt(B, 0) as Diff;

Hope this helps you.

Regards,

Jagan.

• ###### Re: How can I incorporate null values as zero in a calculation?

Try this out,

*,

if(A = ' ', 0, A);

if(B = ' ', 0, B);

A - B = diff

This would do the job.

• ###### Re: How can I incorporate null values as zero in a calculation?

Is it good practice to do the calculations before the joins?

• ###### Re: How can I incorporate null values as zero in a calculation?

Mina

Have you looked at using the Nullasvalue() command in your load script ?

Best Regards,     Bill

• ###### Re: How can I incorporate null values as zero in a calculation?

Hi Bill,

How do I use that in context?

• ###### Re: Re: How can I incorporate null values as zero in a calculation?

Mina

Set NullValue = '0' ;

Nullasvalue [YourFieldName] ;

Best Regards,    Bill

• ###### Re: Re: How can I incorporate null values as zero in a calculation?

thank you Bill, for your prompt response.

• ###### Re: How can I incorporate null values as zero in a calculation?

No, do the joins first then proceed to do the calculations.

• ###### Re: How can I incorporate null values as zero in a calculation?

Thanks to all who replied, I ended on using the following:-

if(isnull(A), 0, (A)) - if(isnull(B), 0, (B)) as Diff;

Although the other suggestions are equally suitable.

Thanks again.