# QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements
QlikView Fans! We’d love to hear from you.
Rules, plus terms and conditions, can be found here.
Contributor

## 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;

1 Solution

Accepted Solutions
Not applicable

## 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.

12 Replies
Not applicable

## 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

Valued Contributor

## 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;

Contributor

## 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

MVP

## 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.

Not applicable

## 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.

Contributor

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

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

MVP

## 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

Not applicable

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

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

Contributor

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

Hi Bill,

How do I use that in context?