[ASK] why the result in sql with the qlikview is different?

[#1]

hello..

i have another problem with developing qlikview

in this case i have to sum something

in sql server they look like this syntax :

SELECT sum((coraccount.angsuran*coraccount.Tenor) - payuser.totbayar)

FROM coraccount

INNER JOIN (SELECT norek,nopin,sum(angsuran) totbayar FROM coraccountdetail WHERE paidsts = '1' GROUP BY norek,nopin) payUser

ON coraccount.norek = payuser.norek AND coraccount.nopin = payuser.nopin

WHERE coraccount.closetype = 0

and the result is : 3,526,454,813

then i try to do the same thing in qlikview, and the syntax is :

=sum(if(closetype_CA='0' and paidsts_CAD='1',(angsuran_CA*tenor_CA))) - $(=Vjumlah_angsuran)

Vjumlah_angsuran is sum(if(paidsts_CAD=1,angsuran_CAD))

and the result is : 324,374,699,644

in qlikview table corAccount and corAccountDetail has already connected through norep and nopin also

and why the different of result is very big?

need help also in this part

many thx..

[#2]

i really need a hand here

i'm kinda stuck

😞

[llauses243 · Accepted solution]

Maybe ...

in qlikview table corAccount and corAccountDetail has already connected through norep and nopin also ... but not connect through coraccount.closetype = 0 then big value ¡¡

Test: sum(if(closetype_CA='0' and paidsts_CAD=1,angsuran_CAD))

good luck

[#4]

I would suggest building your statement a piece at a time, and using a series of tests to validate your assumptions, rather than attempting to 'fix' the whole thing at the same time, not knowing where the fault is.

ie: create just the very basic calculation:

sum(((angsuran_CA*tenor_CA)) - $(=Vjumlah_angsuran))

And make selections in list boxes to match your above stated if criteria, and compare that against your result from SQL. Continue to build on it, until you understand where your statement failed. there is either an issue with your statement, or with the underlying data model. Building through it step by step will identify which it is.

However, basd on looking at your syntax what I'm seeing is in SQL, you've written this: SUM(A*B-C), in QV, you've written this: SUM(A+B) - C; in SQL you're adding a '-C' for every A*B, in QV, you're only doing it once... or at least once that we can see, because we don't know what is in that variable.

[#5]

@Illauses @Ryan

hello..

thx for ur help

this problem is solved now

🙂

many thx..