Qlik Community

Ask a Question

QlikView App Development

Discussion Board for collaboration related to QlikView App Development.

Announcements
Qlik Highlights 2020 Giveaway! Watch, reply and have a chance to win a $200 Amazon Gift Card! Watch Video
cancel
Showing results for 
Search instead for 
Did you mean: 
Creator
Creator

Wrong subtraction during load

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.

     

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

Thanks

SAK

1 Solution

Accepted Solutions

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.

Logic will get you from a to b. Imagination will take you everywhere. - A Einstein

View solution in original post

6 Replies
Master III
Master III

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);

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.

Logic will get you from a to b. Imagination will take you everywhere. - A Einstein

View solution in original post

Champion II
Champion II

please post a sample app that demonstrates the problem

Creator
Creator

"RangeSum(A, -B)"  worked.

Creator
Creator

Both working fine. Thanks a ton.

RangeSum(A, -B)

     or

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

Master III
Master III