Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Here are bunch of mapping tables:
MappingTable1:
Mapping
LOAD ROLE,
[REF BUILD] & '|' & OTHER & '|' & RUN & '|' & Enable
FROM
[..\..\Downloads\Resource Planning.xlsx]
(ooxml, embedded labels, table is ROLES);
Temp:
CrossTable (ROLE, Value)
LOAD [Employee Name],
[Business Relationship (BRM)],
[Architect / PM],
Analyst,
Admin,
[Support & SME],
[Project Integrator],
Specialist,
Technician,
[Manager / Team Leader],
Enable
FROM
[..\..\Downloads\Resource Planning.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
[..\..\Downloads\Resource Planning.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 [EMP. NAME],
Jan,
Feb,
Mar,
Apr,
May,
Jun,
Jul,
Aug,
Sep,
Oct,
Nov,
Dec
FROM
[..\..\Downloads\Resource Planning.xlsx]
(ooxml, embedded labels, table is [Actual Working Days]);
MappingTable4:
Mapping
LOAD [EMP. NAME]&Month(Date#(Month, 'MMM')),
Value3
Resident Temp3;
DROP Table Temp3;
and then one of the M tabs (M1)
M1:
LOAD RowNo() as RowNo,
Month,
[EMP. NAME],
ROLE,
Team,
[TOTAL WORKING DAYS],
SubField(ApplyMap('MappingTable1', ROLE, 0), '|', 1) * ApplyMap('MappingTable2', [EMP. NAME]&ROLE, 0) * ApplyMap('MappingTable3', [EMP. NAME]&Team, 0) * ApplyMap('MappingTable4', [EMP. NAME]&Month(Date#(Month, 'MMMM')), 0) as [REF BUILD],
SubField(ApplyMap('MappingTable1', ROLE, 0), '|', 2) * ApplyMap('MappingTable2', [EMP. NAME]&ROLE, 0) * ApplyMap('MappingTable3', [EMP. NAME]&Team, 0) * ApplyMap('MappingTable4', [EMP. NAME]&Month(Date#(Month, 'MMMM')), 0) as OTHER,
SubField(ApplyMap('MappingTable1', ROLE, 0), '|', 3) * ApplyMap('MappingTable2', [EMP. NAME]&ROLE, 0) * ApplyMap('MappingTable3', [EMP. NAME]&Team, 0) * ApplyMap('MappingTable4', [EMP. NAME]&Month(Date#(Month, 'MMMM')), 0) as RUN,
SubField(ApplyMap('MappingTable1', ROLE, 0), '|', 4) * ApplyMap('MappingTable2', [EMP. NAME]&ROLE, 0) * ApplyMap('MappingTable3', [EMP. NAME]&Team, 0) * ApplyMap('MappingTable4', [EMP. NAME]&Month(Date#(Month, 'MMMM')), 0) as Enable
FROM
[..\..\Downloads\Resource Planning.xlsx]
(ooxml, embedded labels, table is M1);
Like I mentioned, the results are not matching completely, but I think there might be a small mistake here or there
