Join tables in load based on same starting field value

[User12321]

Hi,

I have 2 tables where Table 1 Company is the partial name (starting name of the complete name) and Table 2 FullCompany is the complete name.

I want to join Table 2 to Table 1.

How can I do this in load? Any suggestion please. Thank you.

[chaorenzhu]

I’m pretty sure there is smarter solution. One dumb solution I could think of is to perform an cross join between the two tables, use wildmatch() or substringcount() to compare Company and FullCompany, then filter those records where there is a match

[marcus_sommer · Accepted solution]

You could use a mapping-approach to get the company information into Table2, for example with something like this:

m: mapping load Company, '<<' & Company & '>>' from Table1;

Table2: load *, textbetween(mapsubstring('m', FullCompany), '<<', '>>') as Company
from Table2;

and then you could join/map both tables on the common field Company.

- Marcus