Amount wrong when using sum function

master_student — Thu, 30 Jun 2016 22:24:51 GMT

Hello community,

My queries works fine on qlikview but whene I use the sum function , the amount become wrong as if there's redondoncy on the data here's my queries :

Transaction dis niv 1:

select tt.report_date,

tt.report_type,

tt.ALIAS_NAME,

tt.CREDIT // the amount that is wrong whene using sum function
d.ALIASNAME as alias_debit
from (
SELECT
trans.*,
tab.ALIASNAME alias_credit
FROM POS_REPORT trans , DUMP_RETAILER tab
WHERE
trans.credit_ACTOR_ID=tab.ACTORE_ID(+)
and report_type = 'TSR001'
and debited_ACTOR_ID IN ($(vActore))
AND FUNCTION = 243 ) tt,DUMP_RETAILER d
where tt.debited_actor_id=d.actore_id(+)
and tt.REPORT_DATE >= TO_DATE('01/06/2016','DD/MM/YYYY');

Transaction dis niv 2:

select

tt.report_date,

tt.report_type,

tt.ALIAS_NAME,

tt.CREDIT // the amount that is wrong whene using sum function

, d.aliasname cl2
from (
SELECT
REPORT_TYPE,
REPORT_DATE, DEBITED_ACTOR_ID,
CREDIT_ACTOR_ID,
CREDIT,
tab.ALIASNAME cl1

FROM
POS_REPORT trans,
DUMP_RETAILER tab

WHERE
trans.DEBITED_ACTOR_ID =tab.ACTORE_ID(+)
and REPORT_TYPE = 'TSR001'
AND FUNCTION = 243
and DEBITED_ACTOR_ID IN ($(vCreditActore))
AND OPER_STATE = 61
AND IERRORX = 0) tt, DUMP_RETAILER d
where
tt.CREDIT_ACTOR_ID =d.ACTORE_ID(+)
and tt.REPORT_DATE >= TO_DATE('01/06/2016','DD/MM/YYYY');

Re: Amount wrong when using sum function

master_student — Fri, 01 Jul 2016 11:49:34 GMT

Any help please??

Re: Amount wrong when using sum function

swuehl — Fri, 01 Jul 2016 13:00:48 GMT

I am not sure how we are supposed to help here, since we don't know your data, your requirements, what differentiates 'wrong' from 'correct' etc.

It doesn't even look like a QV specific issue.

I would assume that the JOINs of your tables in SQL duplicate some records and that this is not what you want to achieve when aggregating the CREDIT field, but since we are missing any detailed information, even about what you exactely see and expect to see I can't tell.

topic Re: Amount wrong when using sum function in QlikView

Amount wrong when using sum function

Re: Amount wrong when using sum function

Re: Amount wrong when using sum function