Join tables by text that contains

Dear Community,

Aiming to calculate share of investors types (based on all active and former investors) for each company. As a first step for this, I would like to merge the two tables below.

Table A: Companies with investor names

Company name	Active investor name	Former investor name
Company1	Investor1,Investor5	Investor100,Investor2
Company2	Investor1, Investor2...Investor20	Investor1, Investor2...Investor20
....
Company5000	Investor51	Investor3

Table B: Investors with investor types

Investor name	Investor type
Investor1	Venture Capital
Investor2	Private Equity
....	...
Investor1000	State

The manual way would be to 1) Split text within "active investor name" and "former investor name" columns to several columns by , 2) Join each column via investor name from the second table. But this way I have to perform tons of joins separately as I will end up with hundreds of new columns that need to be joined with second table to get "investor type" - so I was hoping there might be an easier way?

Output table should look like this. If you think there is an easier way to calculate the share of investor types per company, I'm also open to alternative approaches!

Company name	Active investor name 1	Active investor name 1_type	Active investor name 2	Active investor name 2_type	Former investor name 1	Former investor name 1_type	Former investor name 2	Former investor name 2_type
Company1	Investor1	Venture Capital	Investor5	xxx	Investor100	xxx	Investor2	Private Equity

Many thanks in advance!

Regex

Join

Text Mining

Accepted answers

Christina_H

@FraM What do you want the output to look like? You can join while the data is vertical to get all investor types at once, then you just need to rearrange the output back to how you want it.

(Missing investor types are due to the test data being incomplete)

Edit: I've updated my workflow to put everything out sideways, which seems like what you need, see attached.

Crosstab join.yxmd

All comments

Emmanuel_G

@FraM

Find in attachement the way of doing this. I did a test with first line of companies and the first two rows of investors.

Let me know if there is any issue and please, do not hesitate to mark this answer as solution if it helped. 🙂

Join tables by texts (1).yxmd

FraM

@Emmanuel_G

Many thanks for your response! !The part of splitting the investor names to rows and transposing back works well.

However the join for the investor type only works for the specific investor selected via left join (e.g. "former investor 2" in your example). What I would need is a dynamic join to get a new field with the investor type for each active and former investor (some companies have up to 100 active or former investors).

Specifically for this example this would mean:

- If I add "Investor5" (which is the 2nd active investor of company1) with type "impact investor" to table B, I would automatically get a column with "active investor name2_ type" filled with "impact investor" in my output table.

- If I add "Investor100" (which is the 1st former investor of company1) with type "state investor" to table B, I automatically get a column with "former investor name1_type" filled with "state investor" in my output table

Do you have a suggestion how to build such a "dynamic join"?

Christina_H

@FraM What do you want the output to look like? You can join while the data is vertical to get all investor types at once, then you just need to rearrange the output back to how you want it.

(Missing investor types are due to the test data being incomplete)

Edit: I've updated my workflow to put everything out sideways, which seems like what you need, see attached.

Crosstab join.yxmd

Quick Links

This months top contributors

atcodedog05 19458

Qiu 15866

binu_acs 15708

MarqueeCrew 13708

apathetichell 13703