1 Reply Latest reply: Jun 11, 2013 2:41 PM by Tony Strano

# Calculating 2 Tables with Multiple Many to Many Relationships

I am perplexed.

I have 2 data tables:

• One that tracks shipments for Parts, Customers and by Quarter,
• The other tracks returns for a Part, Customer by Quarter. Additionall there is a Point of Failure (POF) and Failure Mechanism (FM)

The 2 tables are joined by these 3 fields

I need to calculate the return rates in Parts per Million PPM = (Returned/Shipped)*1000000.

Where I run into issues is calculating PPM for a given Customer or Part by POF and/or FM

What want to get is

CustomerPOFVolumeRetunredPPM
Customer 1FIeld1,000,00011
Customer 1Production1,000,00022
Customer 10km1,000,0000

What I get is:

CustomerPOFVolumeRetunredPPM
Customer 1FIeld1,000,00011
Customer 1Production1,000,00022
Customer 10km0

I can not come up with a correct Set Analysis where by it will give be back the total volume for the customer unless there is a value for the POF. This is a simplified version and the acutal data, where I get differing values for Volume (which should reman constant) depending on the number of returns......

Thank you fo any thoughts anyone might have..........

• ###### Re: Calculating 2 Tables with Multiple Many to Many Relationships

I won't be able to help you with doing this in set analysis but typically in a Data warehouse with a many to many table query issue,  you need a driving table between the two tables to ease the join to get the results you expect.  I would suggest structuring the data to satisfy the reporting requirement in the load.  that would also simplify the set analysis.

my .02\$

Good luck!