Alteryx Designer Discussions

Find answers, ask questions, and share expertise about Alteryx Designer.
SOLVED

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

CanadaKing
5 - Atom

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

2 REPLIES 2
NicoleJohnson
14 - Magnetar
14 - Magnetar

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.

 

NicoleJohnson_0-1635549781023.png

 

 

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
5 - Atom

Thanks, great solution.

Labels