Join? Link? Map? Help

[Anonymous]

Hi

I have a table of sales by supplier:

Month	Supplier	Sales
Jan	A	1488
Jan	B	1301
Jan	C	1267
Feb	A	1373
Feb	B	1307
Feb	C	1228
Mar	A	1816
Mar	B	1101
Mar	C	1885

And a table of sales by customer:

Month	Customer	Sales
Jan	X	2668
Jan	Y	978
Jan	Z	410
Feb	X	815
Feb	Y	348
Feb	Z	2745
Mar	X	1515
Mar	Y	1734
Mar	Z	1553

I want to create a table that shows sales by customer AND supplier that would look like this:

Month	Supplier	Customer	Sales
Jan	A	X	979
Jan	A	Y	359
Jan	A	Z	150
Jan	B	X	856
Jan	B	Y	314
Jan	B	Z	132
Jan	C	X	833
Jan	C	Y	306
Jan	C	Z	128
Feb	A	X	286
Feb	A	Y	122
Feb	A	Z	964
Feb	B	X	273
Feb	B	Y	116
Feb	B	Z	918
Feb	C	X	256
Feb	C	Y	109
Feb	C	Z	863
Mar	A	X	573
Mar	A	Y	656
Mar	A	Z	587
Mar	B	X	347
Mar	B	Y	398
Mar	B	Z	356
Mar	C	X	595
Mar	C	Y	681
Mar	C	Z	610

I have no other information to link the 2 tables other than date as it's just forecast information.  How is this possible?

Thanks

[#2]

I have a question - both tables have sales but one is for customer and the other is for supplier - If you wnated to put everytung in one table, wouldnt you need sales for both?

if you wnat to create one table from 2 and you want columns from both, you would need to do a join

[Anonymous]

Its just forecast information so I have sales by customer or sales by supplier but I don't have both.  That's what I want to create in the script.  A join doesn't work as I get null values for supplier where there is a customer and vice versa

[#4]

I still do not understand which of the 2 Sales fields you have, you are showing in your consolidated table

attached is the output based on the sample data you provided (I created 2 excel spreadsheets and renamed the sales fileds to avoid synthetic key

are you looking for a relationship between customer and supplier?

[Anonymous]

can you attach the qvw so I can have a look at what you did?

[#6]

sure - see attached

[Anonymous]

based on my data in January supplier A supplied 36.7% of all sales in January therefore supplier A sold 36.7% of sales to customer X.  36.7% of 2668 is 979.  Does that make sense?

[Digvijay_Singh]

I think without knowing which supplier supplies to which customer and the value of sales between them, it wouldn't be possible to create the right model.

[Anonymous]

I just want to assume that proportion of sales by supplier applies to each individual customer.  I know that in January supplier A supplied 36.7% of all sales in January therefore supplier A sold 36.7% of sales to customer X.

[#10]

I agree based on the numbers  in January(total customer sales of 4056) and supplier sales for Supplier A (1488) equates to supplier A accounting for 36.7% of the January.  I am not sure that the percentage is evenly split amongst the customers, unless that is a assumption or understanding you have