topic Re: Qlik Formula in QlikView

.

xyz1 — Sun, 14 Apr 2019 12:58:28 GMT

Re: Qlik Formula

sdmech81 — Thu, 09 Feb 2017 10:17:56 GMT

HI,

You can go through this,there functions like look up and apply map in qlikview

Joins and Lookups

sachin

Re: Qlik Formula

sdmech81 — Thu, 09 Feb 2017 10:18:36 GMT

Or give excel data and mark there what all u want to look up

sachin

.

xyz1 — Sun, 14 Apr 2019 12:59:37 GMT

Re: Qlik Formula

vardhancse — Thu, 09 Feb 2017 10:20:27 GMT

By seeing the script:

most of the things were vlookup, you can use Applymap().

After the above mentioned formula what is the data getting in to the excel?

.

xyz1 — Sun, 14 Apr 2019 13:00:40 GMT

.

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

Re: Qlik Formula

sdmech81 — Thu, 09 Feb 2017 10:34:46 GMT

Hi,

Plss check this out..Its sttaight forward.

QlikView | Scripting | Lookup & ApplyMap Function - YouTube

With this u can map fields frm one to another table based on some attribute.Check above video plss

Sachin

.

xyz1 — Sun, 14 Apr 2019 13:01:09 GMT

Re: Qlik Formula

sunny_talwar — Thu, 09 Feb 2017 10:46:07 GMT

Checking

Re: Qlik Formula

sdmech81 — Thu, 09 Feb 2017 10:50:16 GMT

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

sunny_talwar — Thu, 09 Feb 2017 11:00:29 GMT

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

.

xyz1 — Sun, 14 Apr 2019 13:04:42 GMT

Re: Qlik Formula

sunny_talwar — Thu, 09 Feb 2017 11:09:06 GMT

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

.

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

Re: Qlik Formula

sunny_talwar — Thu, 09 Feb 2017 11:12:24 GMT

Here it is

.

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

Re: Qlik Formula

sunny_talwar — Thu, 09 Feb 2017 12:10:22 GMT

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

.

xyz1 — Sun, 14 Apr 2019 13:01:55 GMT

Re: Qlik Formula

sunny_talwar — Thu, 09 Feb 2017 12:13:06 GMT

what tab?