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
Résolu ! Accéder à la 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
Thanks, great solution.