Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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?
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;
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;
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;
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