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

[qlikmpate0]

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)

Load

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

[#2]

Hi Mena,

Could you please elaborate?

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

[bbi_mba_76]

Hi,

maybe with

join(Temp table)

Load

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

or

join(Temp table)

Load

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

[qlikmpate0]

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

[jagan]

Hi,

Try using Alt() like this

Load

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

Hope this helps you.

Regards,

Jagan.

[#6 · Accepted solution]

Try this out,

Load

*,

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

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

A - B = diff

This would do the job.

[qlikmpate0]

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

[Anonymous]

Mina

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

Best Regards,     Bill

[#9]

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

[qlikmpate0]

Hi Bill,

How do I use that in context?