? - Qlik Community

xyz1 · ‎2017-02-17

.

sunny_talwar · ‎2017-02-17

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 · ‎2017-02-19

.

sunny_talwar · ‎2017-02-20

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 · ‎2017-02-22

.

sunny_talwar · ‎2017-02-22

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

xyz1 · ‎2017-02-22

.

sunny_talwar · ‎2017-02-22

I am not sure I follow

xyz1 · ‎2017-02-22

.

sunny_talwar · ‎2017-02-22

Checking