Join Multiple tables

Kelsmer — Fri, 02 Apr 2021 08:52:09 GMT

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$);

Re: Join Multiple tables

Lisa_P — Sun, 07 Mar 2021 23:18:59 GMT

For each table add a new field that indicates the year.

Table 1

'2016' as FinYear,

Table 2

'2017' as FinYear,

This could also be derived if the original file/sheet name includes the year.

topic Re: Join Multiple tables in App Development

Join Multiple tables

Re: Join Multiple tables