topic Re: load script: lookup field from another table with two matchfields in QlikView

load script: lookup field from another table with two matchfields

Wed, 25 Nov 2020 16:16:04 GMT

Hi,

first of all, I do not want to join tables.

Table1:
Load
Year,
Company,
Data1,
Data2,
Data3
From xls;

Table2:
Load
Year,
Company,
Data1*Data2 as Product
Resident Table1;

Table3;
Load
Year,
Company,
(Data3/lookup('Product','Company',Company,'Table2')) as Totalresult
Resident Table1;

My problem is, that each value depends on the company and the year. As lookup is based on load order I do not get the right results.

Any chance one can also match for the year? Or any other idea?

Re: load script: lookup field from another table with two matchfields

sunny_talwar — Fri, 24 Jun 2016 13:37:07 GMT

How about this:

Table1:

LOAD Year,

Company,

Data1,

Data2,

Data3

From xls;

MappingTable:

Mapping

LOAD Year & '|' & Company,

Data1*Data2 as Product

Resident Table1;

Table3;

LOAD Year,

Company,

(Data3/ApplyMap('MappingTable',Year & '|' & Company) as Totalresult

Resident Table1;

Re: load script: lookup field from another table with two matchfields

Fri, 24 Jun 2016 13:52:01 GMT

Works, many thanks!

Could you explain the use of "Year & '|' & Company"?

And as Mapping seems to be temporary, what is the best way to keep the field product?

This?

Table3;

LOAD Year,

Company,

(Data3/ApplyMap('MappingTable',Year & '|' & Company) as Totalresult

ApplyMap('MappingTable',Year & '|' & Company) as Product

Resident Table1;

Re: load script: lookup field from another table with two matchfields

sunny_talwar — Fri, 24 Jun 2016 13:58:35 GMT

To answer you first question: Mapping Load can only work with 2 fields: One that Maps from and one that Maps 2. In order to map from two fields... I concatenated them together two form a single map from field.

For 2nd question, I think you have the right approach unless you want to keep a stand alone table for any reason.

Best,

Sunny