Skip to Content
0

Measure value doubled

Nov 21, 2017 at 01:39 PM

42

avatar image
Former Member

Hi Experts, My webi report is generating incorrect results with following query: As its doubling the netIncome valueSELECT

  lead.leadId,
    sum(ifnull(applicantEmployment.netIncome,0)),
   sum(ifnull(applicantIncome.amount,0))
FROM
  lead LEFT OUTER JOIN applicant ON (lead.leadId=applicant.leadId  AND  lead.deleted= 0  AND  applicant.deleted = 0)
   LEFT OUTER JOIN applicantEmployment ON (applicantEmployment.leadApplicantId=applicant.leadApplicantId  AND  applicant.deleted = 0  AND  applicantEmployment.deleted =0)
   LEFT OUTER JOIN applicantIncome ON (applicantIncome.leadApplicantId=applicant.leadApplicantId  AND  applicant.deleted = 0  AND  applicantIncome.deleted=0)
WHERE
  lead.leadId  =  800
GROUP BY
  lead.leadId, 
  applicant.leadApplicantId 

Above query results in :

However if you look at below tables sum(ifnull(applicantEmployment.netIncome,0)) should be = 10310.31+23380 = 33690.31 but its coming out as 44000.62 which is 10310.31+10310.31+23380

So it doubled up netincome for applicant id 5339 which has 2 rows in applicant income table.

Is it any type of SQL trap?

result1.jpg (18.1 kB)
result2.jpg (16.8 kB)
10 |10000 characters needed characters left characters exceeded
* Please Login or Register to Answer, Follow or Comment.

1 Answer

AMIT KUMAR
Nov 21, 2017 at 01:57 PM
1

in the webi query properties->uncheck the option "retrieve duplicate rows" and try.

or if still not work then in the universe measure object add DISTINCT and create as dimension object and try.

Show 3 Share
10 |10000 characters needed characters left characters exceeded
Former Member

Thanks Amit for the response.

I realized that question desc wasn't clear and updated the same.

Can you please check and respond again.

0

create your net income object as dimension in universe and uncheck the option "retrieve duplicate rows" option in webi query properties and check.

0
Former Member

Didnt work.

I guess issue is in SQL generated as the other table has 2 rows??

0