Wrong subtraction during load

[Anonymous]

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

[prma7799]

Try like this

Test:

LOAD * , A-B as C ;

LOAD EntryDesc,

     A,

     B

FROM

(ooxml, embedded labels, table is Sheet1);

OR

Test:

LOAD EntryDesc,

     A,

     B,

      A-B as C

FROM

(ooxml, embedded labels, table is Sheet1);

[jonathandienst · Accepted solution]

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)

     or

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

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

[vinieme12]

please post a sample app that demonstrates the problem

[Anonymous]

"RangeSum(A, -B)"  worked.

[Anonymous]

Both working fine. Thanks a ton.

RangeSum(A, -B)

     or

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

[prma7799]