i would like to join two tables to achive the result as shown in: Joint Table. I am using two xlsx inputs to get the data.
Right now I'm using a simple join, and the result is, that I get a table like the Joint Table BUT for all of the companies which don't have a group assigned in Input Table Group table, i get back a "-" -> means the company group field isn't filled.
How to a setup a join to have the following result: If a company group is assigned to the company it should set be included in the company group field. If no company group is assigned to the company in the lookup table, it should use instead of an assigned company group the company na
Hi once you have joined the table you could join the table back on itself using an isnull condition on the Group to create the final Company Group, something like :
main: LOAD Company, Revenue FROM Book1.xls (biff, embedded labels, table is Sheet1$);
Join (main) LOAD Company, [Company Group] as temp_co FROM Book1.xls (biff, embedded labels, table is Sheet2$);
I'd use a mapping table if there is a unique value for each Company, although a JOIN would work as well using the Company field as key field.
RevenueTableMap:
MAPPING LOAD Company,
Revenue
FROM InputTableRevenue;
JointTable:
LOAD Company,
ApplyMap('RevenueTableMap', Company, 'Not found') AS Revenue,
"Company Group"
FROM InputTableGroup;
In its three parameter form, the ApplyMap() function takes the mapping table single quoted as the first parameter, the field which correspondence you want to get as the second parameter, and the third "Not found" in the case above, the default value should any value in the mapping table matches the current value for Company.
.png "Miguel_Angel_Baeyens"){kind=avatar}
