topic Re: Problem with 0 in formula in QlikView

Problem with 0 in formula

Wed, 06 Jul 2016 07:51:35 GMT

Hi community,

i have a problem with NULL values in a formula (see screenshot).

The far right column calculates the difference between the left and the middle column. Apparently, it doesn't work when there's a 0 as you can see in the screenshot.

The table is based on data from excel sheets, and in the excel sheets there isn't actually a "0" , it's just a missing value. I guess it has something to to with this, because once i fill a random value into the blank in the excel sheet manually, the calculation works.

Is there any way to solve this issue in qlikview, or do i have to manipulate the excel sheets (which would not really be an option though)?

Thank you for your help.

Dominic

Re: Problem with 0 in formula

andrespa — Wed, 06 Jul 2016 08:21:50 GMT

Hi Dominic, could you post a sample data on how you're loading the data into Qlikview?

Best regards,

Andrés

Re: Problem with 0 in formula

Wed, 06 Jul 2016 08:24:49 GMT

what exactly do you mean?

Best regards,

Dominic

Re: Problem with 0 in formula

andrespa — Wed, 06 Jul 2016 08:28:05 GMT

Hi again, I think it has to be with null handling as you said before. I don't know how you're loading the data but something like this might be useful:

LOAD*,

B1-A AS C1;

LOAD A,

IF(IsNull(B),0,B) AS B1

FROM

(ooxml, explicit labels, table is Hoja1);

Hope it helps,

Andrés

Re: Problem with 0 in formula

andrespa — Wed, 06 Jul 2016 08:36:32 GMT

Hi, as you see the column B is loaded as a null value from excel (on your case you have activated that all null values to load as zero) then if you transform that value on the load script ( IF(IsNull(B),0,B) AS B1) and then use a precedent load to make your final calculation (B1-A AS C) then as you can see it works.

Hope it's clear.

Andrés

Re: Problem with 0 in formula

effinty2112 — Wed, 06 Jul 2016 12:19:38 GMT

Hi Dominic,

Try as your expression Rangesum(A,-B).

Cheers

Andrew

Re: Problem with 0 in formula

andrespa — Wed, 06 Jul 2016 12:50:33 GMT

This one is much more effective indeed. (didn't figure it out before)

Cheers,

Andrés

Re: Problem with 0 in formula

effinty2112 — Wed, 06 Jul 2016 13:28:16 GMT

Hi Andres,

Yeah Rangesum, Rangecount etc. can be real life savers. Sometimes these QlikView specific functions don't come to mind if you are more familiar with other applications but after you use them for the first time they stick in the brain.

Regards

Andrew

Re: Problem with 0 in formula

Wed, 06 Jul 2016 15:21:49 GMT

Thanks all, i tried Andrew's way and it worked!