Issue with Aggregation Logic Using Conditional Joins Across Multiple Tables

[msinha8]

Hello Gurus,

I am working on the following scenario involving four tables:

Table A

Col1 Col2

a	aref
b	bref

Table B

Col1 Col2

a	x
b	y

Join: Table A.Col1 = Table B.Col1

---

Table C

Col1 Col2

x

100

---

Table D

Col1 Col2

y

200

Join:

• Table C.Col1 = Table B.Col2

• Table D.Col1 = Table B.Col2

---

Goal:

I want to generate the following aggregated result set:

Col1 Measure

aref	100
bref	200

---

My Logic:

• Grouping Dimension: Table A.Col1

• Measure Expression:

  SUM(IFNULL(TableC.Col2, 0)) + SUM(IFNULL(TableD.Col2, 0))

---

Problem:

This approach does not yield the expected result. It either shows a value for a or for b, but not both correctly.

[Venkat · Accepted solution]

Hi msinha,

Create a bridge table containing key columns from Table A and Table B. Join these two tables (Table A and Table B) in the bridge table using the appropriate join type based on their relationship(e.g., inner join or left outer join) using MV. This bridge table can then be used to connect to Table A, Table B, Table C, and Table D.

This is one of the possible approach.

Thanks
Venkat

[msinha8]

Hi Venkat,

I had already implemented the same approach. While it was functioning as expected, I encountered performance issues.

Thanks
MS