Hi all, Please assist here.
how can I join the Two Pivot tables to a Long Sales Table. first Pivot Table where Fin-Year is Equal to 2016 and 2nd pivot table where Fin-Year is Equal to 2017 but while loading both Pivot table from excel files and not as Inline. the only common field is "Pack Size".
2016 Financial Year (1 Jul 2015 to 30 Jun 2016)
| Brand | 1L | 2L | 500ml | 250ml | |
| ColaMax | ZAR 13.50 | ZAR 26.00 | ZAR 8.50 | ZAR 6.00 | |
| FizzyPeach | ZAR 13.50 | ZAR 26.00 | ZAR 8.50 | ZAR 6.00 | |
| GrapeSupreme | ZAR 13.50 | ZAR 26.00 | ZAR 8.50 | ZAR 6.00 | |
| JuicyApple | ZAR 13.50 | ZAR 26.00 | ZAR 8.50 | ZAR 6.00 | |
| PinePop | ZAR 13.50 | ZAR 26.00 | ZAR 8.50 | ZAR 6.00 | |
| SuperMax | ZAR 13.50 | ZAR 26.00 | ZAR 8.50 | ZAR 6.00 | |
| VeryBerry | 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 | |
CrossTable ("Pack Size",Price)
LOAD
Brand,
"1L",
"2L",
"500ml",
"250ml"
FROM [lib://DataFiles/Tables.xlsx]
(ooxml, embedded labels, table is [Table 1]);
2017 Financial Year (1 Jul 2016 to 30 Jun 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 |
CrossTable ("Pack Size",Price)
LOAD
Brand,
"1L",
"2L",
"500ml",
"250ml"
FROM [lib://DataFiles/Tables.xlsx]
(ooxml, embedded labels, table is [Table 2]);
Sales:
LOAD
FinYear,
"Year",
"Month",
Brand,
"Pack Size",
Region,
Channel,
"Sales Qty"
FROM [lib://DataFiles/Sales 2017 and 2016.xls]
(biff, embedded labels, table is Sheet1$);
