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
Customer
POF
Volume
Retunred
PPM
Customer 1
FIeld
1,000,000
1
1
Customer 1
Production
1,000,000
2
2
Customer 1
0km
1,000,000
0
What I get is:
Customer
POF
Volume
Retunred
PPM
Customer 1
FIeld
1,000,000
1
1
Customer 1
Production
1,000,000
2
2
Customer 1
0km
0
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..........
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.
