Problem with 0 in formula

[#1]

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

[andrespa]

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

Best regards,

Andrés

[#3]

what exactly do you mean?

Best regards,

Dominic

[andrespa]

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,

     B,

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

FROM

(ooxml, explicit labels, table is Hoja1);

Hope it helps,

Andrés

[andrespa]

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

[effinty2112 · Accepted solution]

Hi Dominic,

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

Cheers

Andrew

[andrespa]

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

Cheers,

Andrés

[effinty2112]

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

[#9]

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