Multiple crosstables uploads from a single source table

[snehamahaveer]

Hello All,

I have a single excel sheet which provides data for 5 divisions of the company divided across Jobs, hours spent and turnover. I would want the parts of Jobs, hours spent and Turnover to be as a crosstable and then concatenated.

my sample scipt is as follows.

BSNUM_Details:
LOAD
Month as C_Date,
Number & ' - ' & Month as %BS_DateKey,
MonthName(Month) as C_MonthYear,
Year(Month) as C_Year,
Month(Month) as C_Month,
Number as BS_Num,
// Name, //not req
   [Performance Manager],
if(isnull([Approved]),'STD',[Approved]) as [Approved],
if(isnull(Audit),'PENDING',Audit) as Audit,
Enquiries as ABCEnquiries,
Offers as ABCOffers,
[Offer Value] as ABCOfferValue,
[CBC Score] as CBC_Score,
Submissions as CBC_Submissions,
Responses as CBC_Responses,
[Guarantees]
FROM
[$(vOtherDataLocation)\Report - *.xlsx]
(ooxml, embedded labels, header is 1 lines, table is Report);

Division_Details:
CrossTable(CBrand,Jobs,2)
Load
Month as Comp_Month,
Number & ' - ' & Month as %BS_DateKey,
//Number as BS_Num,
Vegetable,
Fruit,
Drinks,
Clothing,
Cosmetics,
Others
FROM
[$(vOtherDataLocation)\Report - *.xlsx]
(ooxml, embedded labels, header is 1 lines, table is Report); 

Extras1:

CrossTable(CBrand,Hours,2)
Load
Month as Comp_Month,
Number & ' - ' & Month as %BS_DateKey,
//Number as BS_Num,
Vegetable1 as Vegetable,
Fruit1 as Fruit,
Drinks1 as Drinks,
Clothing1 as Clothing,
Cosmetics1 as Cosmetics,
Others1 as Others
FROM
[$(vOtherDataLocation)\Report - *.xlsx]
(ooxml, embedded labels, header is 1 lines, table is Report); 

Extras2:

CrossTable(CBrand,Turnover,2)
Load
Month as Comp_Month,
Number & ' - ' & Month as %BS_DateKey,
//Number as BS_Num,
Vegetable2 as Vegetable,
Fruit2 as Fruit,
Drinks2 as Drinks,
Clothing2 as Clothing,
Cosmetics2 as Cosmetics,
Others2 as Others
FROM
[$(vOtherDataLocation)\Report - *.xlsx]
(ooxml, embedded labels, header is 1 lines, table is Report); 

left join(Division_Details)

Load *

Resident Extras1;

left join(Division_Details)

Load *

Resident Extras2;

Drop table Extras1, Extras2;

However, in this case it just sums up data from all 6 divisions and then duplicates the value. Can you please help with this.

I have attached a sample copy of the file used.

Many thanks,

Sneha