Check this script
MappingTable1:
Mapping
LOAD ROLE ,
[REF BUILD] & '|' & Enable & '|' & RUN
FROM [lib://Lib/Resource Planning v1.2.xlsx]
(ooxml, embedded labels, table is ROLES);
Temp:
CrossTable (ROLE, Value,5)
LOAD [Employee Name],
[Location],
[Site],
[Dept],
[I/E],
[Business Relationship (BRM)],
[Architect / PM],
[Analyst],
[Admin],
[Support & SME],
[Project Integrator],
[Specialist],
[Technician],
[Manager / Team Leader],
[Enable]
FROM [lib://Lib/Resource Planning v1.2.xlsx]
(ooxml, embedded labels, table is [Emp vs Role]);
MappingTable2:
Mapping
LOAD [Employee Name]&ROLE,
Value
Resident Temp;
DROP Table Temp;
Temp2:
CrossTable (Team, Value2)
LOAD [Employee Name],
[Alignment & Direction],
Performance,
[Sales, Marketing, and Logistics],
[Industrial, Quality, and Research],
[Finance, Purchasing, HR, & IT],
[Web Application Systems],
[Content Collaboration],
[Business Intelligence],
[B2B / EDI],
[Data Mart Management],
Testing,
[Innovation Center],
[Engineering and Architecture],
[Infrastructure and Operations],
OCM,
[Security and Risk],
[Client Services],
[Access Rights],
Symphony
FROM [lib://Lib/Resource Planning v1.2.xlsx]
(ooxml, embedded labels, table is [Emp vs Team]);
MappingTable3:
Mapping
LOAD [Employee Name]&Team,
Value2
Resident Temp2;
DROP Table Temp2;
Temp3:
CrossTable(Month, Value3)
LOAD [Employee Name],
Jan,
Feb,
Mar,
Apr,
May,
Jun,
Jul,
Aug,
Sep,
Oct,
Nov,
Dec
FROM [lib://Lib/Resource Planning v1.2.xlsx]
(ooxml, embedded labels, table is [Actual Working Days]);
MappingTable4:
Mapping
LOAD [Employee Name]&Month(Date#(Month, 'MMM')),
Value3
Resident Temp3;
DROP Table Temp3;
FOR i = 1 to 12
Table:
LOAD RowNo() as RowNo,
Month,
[Employee Name],
ROLE,
Team,
[TOTAL WORKING DAYS],
SubField(ApplyMap('MappingTable1', ROLE, 0), '|', 1) * ApplyMap('MappingTable2', [Employee Name]&ROLE, 0) * ApplyMap('MappingTable3', [Employee Name]&Team, 0) * ApplyMap('MappingTable4', [Employee Name]&Month(Date#(Month, 'MMMM')), 0) as [REF BUILD],
SubField(ApplyMap('MappingTable1', ROLE, 0), '|', 2) * ApplyMap('MappingTable2', [Employee Name]&ROLE, 0) * ApplyMap('MappingTable3', [Employee Name]&Team, 0) * ApplyMap('MappingTable4', [Employee Name]&Month(Date#(Month, 'MMMM')), 0) as Enable,
SubField(ApplyMap('MappingTable1', ROLE, 0), '|', 3) * ApplyMap('MappingTable2', [Employee Name]&ROLE, 0) * ApplyMap('MappingTable3', [Employee Name]&Team, 0) * ApplyMap('MappingTable4', [Employee Name]&Month(Date#(Month, 'MMMM')), 0) as RUN,
SubField(ApplyMap('MappingTable1', ROLE, 0), '|', 1) * ApplyMap('MappingTable2', [Employee Name]&ROLE, 0) * ApplyMap('MappingTable3', [Employee Name]&Team, 0) as [REF BUILD1],
SubField(ApplyMap('MappingTable1', ROLE, 0), '|', 2) * ApplyMap('MappingTable2', [Employee Name]&ROLE, 0) * ApplyMap('MappingTable3', [Employee Name]&Team, 0) as Enable1,
SubField(ApplyMap('MappingTable1', ROLE, 0), '|', 3) * ApplyMap('MappingTable2', [Employee Name]&ROLE, 0) * ApplyMap('MappingTable3', [Employee Name]&Team, 0) as RUN1
FROM [lib://Lib/Resource Planning v1.2.xlsx]
(ooxml, embedded labels, table is M$(i));
Next
Left Join (Table)
LOAD [Employee Name],
[Location],
[Site],
[Dept],
[I/E]
FROM [lib://Lib/Resource Planning v1.2.xlsx]
(ooxml, embedded labels, table is [Emp vs Role]);
