MDX: count records in table A that are not in table B

Hello all,

I would like to use MDX to identify "contracts" which are in a table A and not in a table B, and count them to have a total per "company".

It should be something like:

Contract / Flag contracts not in table B (1=yes, 0=not)

Contract 1 1

Contract 2 0

Contract 3 1

Contract 4 1

Contract 5 0

Contract 6 0

TOTAL 3

So far I know how to distinguish at contract level which records belong to table A and not table B, but I do not have the total for the company.

In SQL it should be something like that:

SELECT Company,count(*)

FROM tableA

left join tableB

on tableA.Contract = tableB.Contract

WHERE tableB.Contract is null

GROUP BY Company

Thanks,

Guillaume

Assuming that cube uses Table A for fact data, in Data Source View you could replace Table A with a Named Query like:

SELECT tableA.*,

case when tableB.Contract is null

then 1 else 0 end as NotInTableB

FROM tableA

left join tableB

on tableA.Contract = tableB.Contract

Then add a "sum" measure on the new field: NotInTableB.

|||

Hi Deepak,

Thank you for your reply. It works fine now (I used a view compared to the named query).

Finally MDX was not necessary in that case...

Guillaume