?

[xyz1]

.

[sunny_talwar]

1) Do you really need to create one table for each of your Excel tab? I would suggest to create a loop to load M1 to M12 and then may be add flag for each tab

2) Left Join Value, I/E, Dept, Location & Site to your FinalTable based on the key field. Can you share that Excel again?

[xyz1]

.

[sunny_talwar]

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]);

[xyz1]

.

[sunny_talwar]

Which object am I looking at? Some of them are not available to me...

[xyz1]

.

[sunny_talwar]

I am not sure I follow

[xyz1]

.

[sunny_talwar]

Checking