load script: lookup field from another table with two matchfields

[#1]

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?

[sunny_talwar · Accepted solution]

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;

[#3]

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;

[sunny_talwar]

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