topic Re: how to use fields if table is dropped because of mapping in QlikView

?

xyz1 — Sun, 14 Apr 2019 13:16:31 GMT

Re: how to use fields if table is dropped because of mapping

sunny_talwar — Fri, 17 Feb 2017 12:24:56 GMT

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 — Sun, 14 Apr 2019 13:14:11 GMT

Re: how to use fields if table is dropped because of mapping

sunny_talwar — Tue, 21 Feb 2017 01:18:49 GMT

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 — Sun, 14 Apr 2019 13:15:29 GMT

Re: how to use fields if table is dropped because of mapping

sunny_talwar — Wed, 22 Feb 2017 09:36:59 GMT

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

?

xyz1 — Sun, 14 Apr 2019 13:17:33 GMT

Re: how to use fields if table is dropped because of mapping

sunny_talwar — Wed, 22 Feb 2017 10:00:21 GMT

I am not sure I follow

?

xyz1 — Sun, 14 Apr 2019 13:18:03 GMT

Re: how to use fields if table is dropped because of mapping

sunny_talwar — Wed, 22 Feb 2017 11:50:10 GMT

Checking

Re: how to use fields if table is dropped because of mapping

sunny_talwar — Wed, 22 Feb 2017 11:59:06 GMT

I think since you are doing the calculation in the script, you are loosing the ability to sum team on the front end. Is there a reason you want to do this in the script? Can we move this to the front end?

?

xyz1 — Sun, 14 Apr 2019 13:44:02 GMT

?

xyz1 — Sun, 14 Apr 2019 13:30:59 GMT

Re: how to use fields if table is dropped because of mapping

sunny_talwar — Wed, 22 Feb 2017 12:11:34 GMT

Can you make a change like this in your script and rerun it for me?

SubField(ApplyMap('MappingTable1', ROLE, 0), '|', 1) as [REF BUILD1],

ApplyMap('MappingTable2', [Employee Name]&ROLE, 0) as [REF BUILD2],

ApplyMap('MappingTable3', [Employee Name]&Team, 0) as [REF BUILD3],

ApplyMap('MappingTable4', [Employee Name]&Month(Date#(Month, 'MMMM')), 0) as [REF BUILD4],

SubField(ApplyMap('MappingTable1', ROLE, 0), '|', 2) as Enable1,

ApplyMap('MappingTable2', [Employee Name]&ROLE, 0) as Enable2,

ApplyMap('MappingTable3', [Employee Name]&Team, 0) as Enable3,

ApplyMap('MappingTable4', [Employee Name]&Month(Date#(Month, 'MMMM')), 0) as Enable4,

Essentially create a new field instead of multiplying them into 1. Do it for Enable, REF BUILD and Run

?

xyz1 — Sun, 14 Apr 2019 13:29:26 GMT

Re: how to use fields if table is dropped because of mapping

sunny_talwar — Wed, 22 Feb 2017 12:17:26 GMT

Can you reload the attached application for me

?

xyz1 — Sun, 14 Apr 2019 13:28:14 GMT

Re: how to use fields if table is dropped because of mapping

sunny_talwar — Wed, 22 Feb 2017 12:31:40 GMT

Will check it in a bit. Incorrect numbers is okay

?

xyz1 — Sun, 14 Apr 2019 13:28:47 GMT

Re: how to use fields if table is dropped because of mapping

sunny_talwar — Wed, 22 Feb 2017 14:12:24 GMT

Its complex... I am looking at it while doing my own work... not sure if I will get to the bottom of it, but I am trying