Associating 2 Excel sheets, one with one worksheet and the other multiple

[mervin8911]

Hi there

I am working on a dashboard and have 2 excel workbooks. One has a summary of products sold but no sales amounts, just quantity. My Sales figures are on a different workbook, but they are on different tabs and summarized by year, size of product and product type. I need to associate 3 tables, my year, brand, pack size and quantity to get my total sales value of each brand. Is there anyone who can assist in me associating the tables to get the information I need. The first table below is the summary of sales for the reporting period.

The tables below that are a summary of what the product cost by brand and size. As you can see its a summary and need to be associated with the data on the fist table to get the overall sales. The table below has more than 17000 rows, this needs to be associated with the tables below which only has 8 rows. 

Fin Year	Year	Month	Brand	Pack Size	Region	Channel	Sales Qty
2016	2015	Aug	Cola Max	1 L	Eastern Cape	Distributors	652
2016	2015	Aug	Cola Max	1 L	Eastern Cape	Forecourts	567
2016	2015	Aug	Cola Max	1 L	Eastern Cape	Restaurant Chains	227
2016	2015	Aug	Cola Max	1 L	Eastern Cape	Supermarket Chains	1389
2016	2015	Aug	Cola Max	1 L	Gauteng	Distributors	794
2016	2015	Aug	Cola Max	1 L	Gauteng	Forecourts	680
2016	2015	Aug	Cola Max	1 L	Gauteng	Restaurant Chains	1361

Cost of products in 2016

Brand	1l	2L	500ml	250ml
Cola Max	ZAR 13.50	ZAR 26.00	ZAR 8.50	ZAR 6.00
Fizzy Peach	ZAR 13.50	ZAR 26.00	ZAR 8.50	ZAR 6.00
Grape Supreme	ZAR 13.50	ZAR 26.00	ZAR 8.50	ZAR 6.00
Juicy Apple	ZAR 13.50	ZAR 26.00	ZAR 8.50	ZAR 6.00
Pine Pop	ZAR 13.50	ZAR 26.00	ZAR 8.50	ZAR 6.00
Super Max	ZAR 13.50	ZAR 26.00	ZAR 8.50	ZAR 6.00
Very Berry	ZAR 13.50	ZAR 26.00	ZAR 8.50	ZAR 6.00
XtremeOrange	ZAR 18.50	ZAR 26.00	ZAR 13.00	ZAR 9.00

Cost of product in 2017

Brand	1L	2L	500ml	250ml
ColaMax	ZAR 15.00	ZAR 28.00	ZAR 11.00	ZAR 8.00
FizzyPeach	ZAR 15.00	ZAR 28.00	ZAR 11.00	ZAR 8.00
GrapeSupreme	ZAR 15.00	ZAR 28.00	ZAR 11.00	ZAR 8.00
JuicyApple	ZAR 15.00	ZAR 28.00	ZAR 11.00	ZAR 8.00
PinePop	ZAR 15.00	ZAR 28.00	ZAR 11.00	ZAR 8.00
SuperMax	ZAR 15.00	ZAR 28.00	ZAR 11.00	ZAR 8.00
VeryBerry	ZAR 15.00	ZAR 28.00	ZAR 11.00	ZAR 8.00
XtremeOrange	ZAR 22.00	ZAR 40.00	ZAR 17.00	ZAR 11.00

[teiswamsler · Accepted solution]

Hi Mervin

You change your model a little and unpivot your cost table:

Br. Teis

See script below.

Fact:
Load
*,
Brand &'|'& [Pack Size] &'|'& [Fin Year] as CombinedKey
inline [
Fin Year, Year, Month, Brand, Pack Size, Region, Channel, Sales Qty
2016, 2015, Aug, Cola Max, 1L, Eastern Cape, Distributors, 652
2016, 2015, Aug, Cola Max, 1L, Eastern Cape, Forecourts, 567
2016, 2015, Aug, Cola Max, 1L, Eastern Cape, Restaurant Chains, 227
2016, 2015, Aug, Cola Max, 1L, Eastern Cape, Supermarket Chains, 1389
2016, 2015, Aug, Cola Max, 1L, Gauteng, Distributors, 794
2016, 2015, Aug, Cola Max, 1L, Gauteng, Forecourts, 680
2016, 2015, Aug, Cola Max, 1L, Gauteng, Restaurant Chains, 1361
];

[tmp_Brand ans size]:
Load
*,
'2016' as Year
inline [
Brand, 1L, 2L, 500ml, 250ml,
Cola Max, ZAR 13.50, ZAR 26.00, ZAR 8.50, ZAR 6.00
Fizzy Peach, ZAR 13.50, ZAR 26.00, ZAR 8.50, ZAR 6.00
Grape Supreme, ZAR 13.50, ZAR 26.00, ZAR 8.50, ZAR 6.00
Juicy Apple, ZAR 13.50, ZAR 26.00, ZAR 8.50, ZAR 6.00
Pine Pop, ZAR 13.50, ZAR 26.00, ZAR 8.50, ZAR 6.00
Super Max, ZAR 13.50, ZAR 26.00, ZAR 8.50, ZAR 6.00
Very Berry, ZAR 13.50, ZAR 26.00, ZAR 8.50, ZAR 6.00
XtremeOrange, ZAR 18.50, ZAR 26.00, ZAR 13.00, ZAR 9.00
];

Concatenate([tmp_Brand ans size])
Load
*,
'2017' as Year
Inline [
Brand, 1L, 2L, 500ml, 250ml,
ColaMax, ZAR 15.00, ZAR 28.00, ZAR 11.00, ZAR 8.00,
FizzyPeach, ZAR 15.00, ZAR 28.00, ZAR 11.00, ZAR 8.00,
GrapeSupreme, ZAR 15.00, ZAR 28.00, ZAR 11.00, ZAR 8.00,
JuicyApple, ZAR 15.00, ZAR 28.00, ZAR 11.00, ZAR 8.00,
PinePop, ZAR 15.00, ZAR 28.00, ZAR 11.00, ZAR 8.00,
SuperMax, ZAR 15.00, ZAR 28.00, ZAR 11.00, ZAR 8.00,
VeryBerry, ZAR 15.00, ZAR 28.00, ZAR 11.00, ZAR 8.00,
XtremeOrange, ZAR 22.00, ZAR 40.00, ZAR 17.00, ZAR 11.00
]
;

[Unpivot_Brand ans size]:
CROSSTABLE ([Pack Size],[Cost],2)
LOAD
Year,
Brand,
[1L],
[2L],
[500ml],
[250ml]
RESIDENT [tmp_Brand ans size];

DROP TABLE [tmp_Brand ans size];

[Brand and size]:
Load
*,
Brand &'|'& [Pack Size] &'|'& [Year] as CombinedKey
Resident [Unpivot_Brand ans size];

Drop field Brand, [Pack Size], [Year] from [Brand and size];

Drop table [Unpivot_Brand ans size];

[mervin8911]

Thanks for the solution. Took me a while but finally got it to work.