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

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

qlikmpate0 — Thu, 21 Nov 2013 08:31:43 GMT

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;

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

Thu, 21 Nov 2013 08:37:45 GMT

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

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

bbi_mba_76 — Thu, 21 Nov 2013 08:44:00 GMT

Hi,

maybe with

join(Temp table)

Load

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

join(Temp table)

Load

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

qlikmpate0 — Thu, 21 Nov 2013 08:46:52 GMT

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?

jagan — Thu, 21 Nov 2013 08:59:04 GMT

Hi,

Try using Alt() like this

Load

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

Thu, 21 Nov 2013 09:00:06 GMT

Try this out,

Load

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?

qlikmpate0 — Thu, 21 Nov 2013 09:22:39 GMT

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

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

Anonymous — Thu, 21 Nov 2013 09:27:03 GMT

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?

Thu, 21 Nov 2013 09:32:13 GMT

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

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

qlikmpate0 — Thu, 21 Nov 2013 09:45:29 GMT

Hi Bill,

How do I use that in context?

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

Anonymous — Thu, 21 Nov 2013 10:16:57 GMT

Mina

Before your Load statement in your load script add a couple of lines similar to this :

Set NullValue = '0' ;

Nullasvalue [YourFieldName] ;

Load .......................

Best Regards, Bill

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

qlikmpate0 — Thu, 21 Nov 2013 11:10:52 GMT

thank you Bill, for your prompt response.

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

qlikmpate0 — Thu, 21 Nov 2013 11:13:14 GMT

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.