Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Dear all,
assume following data
Data:
LOAD * INLINE [
Company, Sector, Year, DataX, DataY, DataZ, DataA
A, Financials, 2010, 1, 3, 1, 9
A, Financials, 2011, 9, 9, 1, 0
A, Financials, 2012, 1, 2, 3, 0
B, Financials, 2010, 2, 3, 1, 0
B, Financials, 2011, 2, 77, 1,3
B, Financials, 2012, 2, 3, 4, 0
C, Automotive, 2010, 4, 5, 10, 2
C, Automotive, 2011, 2, 6, 18, 2
C, Automotive, 2012, 3, 5, 10, 1
D, Automotive, 2010, 4, 5, 10, 2
D, Automotive, 2011, 2, 6, 18, 2
D, Automotive, 2012, 3, 5, 10, 1
];
I would like to calculate the market share for any company and any year in its sector based on DataX.
With set analysis it should be something like when defining year and company in listboxes =sum(DataX)/sum({<Company=>}DataX)
What is the best way to do this in the script as I cannot use set analysis?
Example: Market Share Company A, 2010: 1/(1+2)=0.33
May be like this:
Data:
LOAD * INLINE [
Company, Sector, Year, DataX, DataY, DataZ, DataA
A, Financials, 2010, 1, 3, 1, 9
A, Financials, 2011, 9, 9, 1, 0
A, Financials, 2012, 1, 2, 3, 0
B, Financials, 2010, 2, 3, 1, 0
B, Financials, 2011, 2, 77, 1,3
B, Financials, 2012, 2, 3, 4, 0
C, Automotive, 2010, 4, 5, 10, 2
C, Automotive, 2011, 2, 6, 18, 2
C, Automotive, 2012, 3, 5, 10, 1
D, Automotive, 2010, 4, 5, 10, 2
D, Automotive, 2011, 2, 6, 18, 2
D, Automotive, 2012, 3, 5, 10, 1
];
MappingTable:
Mapping
LOAD Sector&Year,
Sum(DataX)
Resident Data
Group By Sector, Year;
FinalData:
LOAD *,
DataX/ApplyMap('MappingTable', Sector&Year) as [Market Share]
Resident Data;
DROP Table Data;
May be like this:
Data:
LOAD * INLINE [
Company, Sector, Year, DataX, DataY, DataZ, DataA
A, Financials, 2010, 1, 3, 1, 9
A, Financials, 2011, 9, 9, 1, 0
A, Financials, 2012, 1, 2, 3, 0
B, Financials, 2010, 2, 3, 1, 0
B, Financials, 2011, 2, 77, 1,3
B, Financials, 2012, 2, 3, 4, 0
C, Automotive, 2010, 4, 5, 10, 2
C, Automotive, 2011, 2, 6, 18, 2
C, Automotive, 2012, 3, 5, 10, 1
D, Automotive, 2010, 4, 5, 10, 2
D, Automotive, 2011, 2, 6, 18, 2
D, Automotive, 2012, 3, 5, 10, 1
];
MappingTable:
Mapping
LOAD Sector&Year,
Sum(DataX)
Resident Data
Group By Sector, Year;
FinalData:
LOAD *,
DataX/ApplyMap('MappingTable', Sector&Year) as [Market Share]
Resident Data;
DROP Table Data;