Measure value doubled

[former_member185138]

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?

[amitrathi239]

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.

[former_member185138]

Thanks Amit for the response.

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

Can you please check and respond again.

[amitrathi239]

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

[former_member185138]

Didnt work.

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