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
