I want to use Column values in Tbl A to identify all records with matching words in Tbl B

[CanadaKing]

Hi,

I have these below tables:

Table A                                                                                                 

Column_1	Result
A and B of C	1, 2
D and E of F	2
G and H	3
J	4
K and A of N	4, 1, 2, 5
M, P and S	5, 6, 7
A, G, M, P, And S	1, 2, 3, 5, 6, 7
X	8

Table B

ID	Description
1	A, B and C charts in alphabet
2	A, B, C, D, E and F  charts in alphabet
3	G, H and I  charts in alphabet
4	J, K and L charts in alphabet
5	M, N and O charts in alphabet
6	P, Q and R charts in alphabet
7	S, T and U charts in alphabet
8	V, W and X charts in alphabet

After using find\replace function to remove conjunction words [and, of, …] in [Column1] in Table A, I want to use [Column1] values in Table A to identify all records with matching words in Table B [Description] column and place results of all partially or fully matching records ID’s (from Table B) in [Result] column in Table A as presented.

How could I do that in Alteryx?

Thanks

[NicoleJohnson · Accepted solution]

There are likely a few different methods for solving this one, and you'll surely need to tweak to make it work with your "real" data, but here is a possible solution...

The key for this method is identifying all the "lookup words" from Tbl A, and getting them into rows (use a Parse tool to split by whatever delimiter you decide to use - I chose a | pipe delimiter)... and then append all those values to Tbl B. Then, for each combo, check to see if the Tbl A value is contained in Tbl B value. At the end, a series of Group Bys + Concatenate will give you the Result field you're looking for.

HOWEVER...

This will be less efficient if you end up with a scenario where you have a LOT of records in Tbl A, because appending a lot of records to every record in a Tbl will create... a lot a lot of records. So if that's the case, you might instead look at splitting every "word" in Tbl B as well, and then doing a simple join between the two data sets. Then, you can group by the Record IDs from both Tbl A and Tbl B that match from the Join output, and then Group by & concatenate to get your final result data set. 

Hopefully that makes sense/points you in the right direction! If you need a little more guidance, let me know 🙂 

Cheers! 

NJ

[CanadaKing]

Thanks, great solution.