Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
qlikmpate0
Creator II
Creator II

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)

Load

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

1 Solution

Accepted Solutions
Not applicable

Try this out,

Load

*,

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

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

A - B = diff

This would do the job.

View solution in original post

12 Replies
Not applicable

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
Partner - Specialist
Partner - Specialist

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
Creator II
Creator II
Author

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
Luminary Alumni
Luminary Alumni

Hi,

Try using Alt() like this

Load

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

Hope this helps you.

Regards,

Jagan.

Not applicable

Try this out,

Load

*,

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

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

A - B = diff

This would do the job.

qlikmpate0
Creator II
Creator II
Author

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

Anonymous
Not applicable

Mina

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

Best Regards,     Bill

Not applicable

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

qlikmpate0
Creator II
Creator II
Author

Hi Bill,

How do I use that in context?