Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

sdmech81
Valued Contributor

Re: Qlik Formula

Hi,I don't knw excel much.

If u can tell me frm which sheet to which columns u want map/lookup,then I can help u.

Wait Sunny said he is checking,else u mark columns to be mapped in excel..

Re: Qlik Formula

I couldn't get the exact result, but you will get an idea here

xyz1
Contributor III

.

.

Re: Qlik Formula

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

Capture.PNG

xyz1
Contributor III

.

.

Re: Qlik Formula

Here it is

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

View solution in original post

xyz1
Contributor III

.

.

Highlighted

Re: Qlik Formula

location? what tab was this on (in the Excel)

xyz1
Contributor III

.

.

Re: Qlik Formula

what tab?