topic Re: Issue with Aggregation Logic Using Conditional Joins Across Multiple Tables in Dashboards & Analytics Discussions

Issue with Aggregation Logic Using Conditional Joins Across Multiple Tables

msinha8 — Sun, 01 Jun 2025 13:42:45 GMT

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

100

Table D

Col1 Col2

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.

Re: Issue with Aggregation Logic Using Conditional Joins Across Multiple Tables

Venkat — Mon, 02 Jun 2025 07:25:33 GMT

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

Re: Issue with Aggregation Logic Using Conditional Joins Across Multiple Tables

msinha8 — Mon, 02 Jun 2025 11:21:38 GMT

Hi Venkat,

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

Thanks
MS