Skip to Content
S N

Measure value doubled

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)
Add comment
10|10000 characters needed characters exceeded

  • Get RSS Feed

1 Answer

  • Nov 21, 2017 at 01:57 PM

    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.

    Add comment
    10|10000 characters needed characters exceeded