Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi Experts,
i have data in a pivot table as attached excel file, how to convert as normal load.
How to i load this kind of data, i have to calculate KPI's based on Product A,B &C, highest Market sales in Product A,B & C, Highest Product Sale,... etc.
Kindly some one please help me.
Regards,
Shanu.
Hi Shanu, you should use the function CrossTable() to unpivot the data. As for the Product Group and the two KPIs, you need some scripting. The below script will work for you to get the data into a straight table:
The resulting straight table is attached as PNG.
ProductCategory_map:
Mapping Load * Inline [
Sales Category, Product Category
s, Product A
1, Product B
2, Product C
];
[Sheet1_tmp]:
CROSSTABLE ([Sales Category],[Sales Amount],6)
LOAD
[Doctor ID],
[First Name],
[Last name],
[City],
[Zip],
[Specialty],
[Product Sales],
[Market Sales],
[Product Sales1],
[Market Sales1],
[Product Sales2],
[Market Sales2]
FROM [lib://Desktop/Sample Data.xlsx]
(ooxml, embedded labels, header is 1 lines, table is Sheet1);
[Sheet1]:
NoConcatenate Load
[Doctor ID],
[First Name],
[Last name],
[City],
[Zip],
[Specialty],
ApplyMap('ProductCategory_map',right([Sales Category],1),'N/A') as [Product Group],
if(left([Sales Category],1) = 'P',[Sales Amount],null()) as [Product Sales]
Resident [Sheet1_tmp]
Where left([Sales Category],1) = 'P';
Left Join([Sheet1])
Load
[Doctor ID],
[First Name],
[Last name],
[City],
[Zip],
[Specialty],
ApplyMap('ProductCategory_map',right([Sales Category],1),'N/A') as [Product Group],
if(left([Sales Category],1) = 'M',[Sales Amount],null()) as [Market Sales]
Resident [Sheet1_tmp]
Where left([Sales Category],1) = 'M';
Drop Table [Sheet1_tmp];
Hope it helps!
Thanks Tim
It's working...
Great! Please mark the comment as "The Solution" so that others can find it.