innerjoin two tables based on the first word in the key columns

in Matrix A I have the following columns:

Date AcquirorName TargetName

_____ ________________ _________________________________

41640 "Investor Group" "Audio Visual Services Group Inc"

In Matrix B I have the following columns:

Date AcquirorName TargetName

_____ ________________ _________________________________

41640 "Investor Group Ltd" "Audio Visual Svcs Group Inc"

I tried to match them using innerjoin function

Akeys={'Date','AcquirorName','TargetName'};
Bkeys={'Date','AcquirorName','TargetName'};
C=innerjoin(A,B,'LeftKeys',Akeys, 'RightKeys',Bkeys);

However, I received no match simply because the names in A and B differ a bit for instance, Ltd is added to the AcquirorName in column 2 of Matrix B and Services is abbreviated to Svcs in Target name in Matrix B

I have more than 300,000 rows so judging visusally is not option although clearly the two rows in A and B match. I am wondering if there is a way to match based on the first word in each column. In this case the date is the same," Investor" under acquiror name is the same and "Audio" under target name is the same. I noticed that almost always the first words are the same.

Answer 1

[Peter Perkins]

The literal answer to your question is to create a new key variable pair from the first word of those two variables.

Probably you ought to be using categorical variables for those variables. Then you can pretty easily clean up the differences in spelling etc., and you are all set. Also: faster.