Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements
QlikView Fans! We’d love to hear from you.
Share your QlikView feedback with the product team… Click here to participate in our 5-minute survey.
Rules, plus terms and conditions, can be found here.
qlikmpate0
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)

Load

*,
(
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,

Load

*,

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,

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
Valued Contributor

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

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

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

Hi,

Try using Alt() like this

Load

*,
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,

Load

*,

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

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

A - B = diff

This would do the job.

qlikmpate0
Contributor

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

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.

qlikmpate0
Contributor

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

Hi Bill,

How do I use that in context?

Community Browser