topic Re: Wrong subtraction during load in QlikView

Wrong subtraction during load

Anonymous — Tue, 20 Jun 2017 09:34:29 GMT

Hi All,

I just can't get my head around what's WRONG with simple subtraction. I am loading from excel. I tried num(A)-num(B) as C but still the the same wrong subtraction is coming. While I am checking in excel, it should be 530 million. Please help. The script is simple.

Load A, B, A-B as C from ....

The sum of A and B is correct but the difference is not coming correct.

EntryDesc	A	B	A-B as C
Actuals	967,305,225.24	436,323,183.98	409,016,914.02	Wrong Subtraction with load
Actuals	967,305,225.24	436,323,183.98	530,982,041.26	Correct one should be

Thanks

SAK

Re: Wrong subtraction during load

prma7799 — Tue, 20 Jun 2017 09:39:18 GMT

Try like this

Test:

LOAD * , A-B as C ;

LOAD EntryDesc,

FROM

(ooxml, embedded labels, table is Sheet1);

Test:

LOAD EntryDesc,

A-B as C

FROM

(ooxml, embedded labels, table is Sheet1);

Re: Wrong subtraction during load

jonathandienst — Tue, 20 Jun 2017 09:47:03 GMT

If either A or B is null, then the sum of A-B is null, even if the other field has a value.You can use something like

RangeSum(A, -B)

Alt(A, 0) - Alt(B, 0)

which will ensure that the null value is treated as a 0.

Re: Wrong subtraction during load

vinieme12 — Tue, 20 Jun 2017 09:48:20 GMT

please post a sample app that demonstrates the problem

Re: Wrong subtraction during load

Anonymous — Tue, 20 Jun 2017 10:30:25 GMT

"RangeSum(A, -B)" worked.

Re: Wrong subtraction during load

Anonymous — Tue, 20 Jun 2017 10:34:35 GMT

Both working fine. Thanks a ton.

RangeSum(A, -B)

Alt(A, 0) - Alt(B, 0)

Re: Wrong subtraction during load

prma7799 — Tue, 20 Jun 2017 10:34:37 GMT