Calculating 2 Tables with Multiple Many to Many Relationships

Tue, 11 Jun 2013 17:57:46 GMT

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..........

Re: Calculating 2 Tables with Multiple Many to Many Relationships

Tony_Strano — Tue, 11 Jun 2013 18:41:01 GMT

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!

topic Re: Calculating 2 Tables with Multiple Many to Many Relationships in QlikView

Calculating 2 Tables with Multiple Many to Many Relationships

Re: Calculating 2 Tables with Multiple Many to Many Relationships