Community Spring Cleaning week is here! Join your fellow Maveryx in digging through your old posts and marking comments on them as solved. Learn more here!

Alteryx Designer Desktop Discussions

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

Lookup table containing single words to be matched with each row of comments field in file

chanikya
6 - Meteoroid

I'm trying to find a solution to do a lookup function. I have a  lookup table containing around 50 rows of words/phrases which need to be checked to a file containing around 300 survey responses  open end answers.  I initially used the find and replace tools but it will only find the first match of the lookup table row in the comment and stop. I want for each of the survey response open end all the 50 words compared to find a match. Please let me know if there is any easy way to handle this.

 

ex : 

Lookup table :

1.BAT

2.MAT

3.CAT

4.SIT

 

Comments: 

1.I have a bat with my cat

2.I sit on a mat with my bat

 

Current output using find and replace too (I used option append fields to record)

1.I have a bat with my cat -BAT

2.I sit on a mat with my bat -SIT

 

Expected output:

1.I have a bat with my cat -BAT,CAT

2.I sit on a mat with my bat -SIT,MAT,BAT

 

Thank you in advance for any help!

8 REPLIES 8
danilang
19 - Altair
19 - Altair

Hi @chanikya 

 

Try this technique.

 

Workflow.png

 

Split your comments into individual words and match these to the look up words.  Concatenate all the matches grouped by RecordID and join back to the original comments

 

Results.png

 

Dan

chanikya
6 - Meteoroid

Hello @

 

I did try this technique. Practically this won't be efficient because I work with hundreds of responses and each response is around a 100 words at least.The file which has comments also have multiple fields in it. To add to that i also have some cases to look for phrases in the text. It would be secondary to get to phrases but I would at least try to find a effective solution for single words. Thank you for putting this solution together. 

 

Please let me know if it makes sense. Real time data is more complex and difficult to handle using this approach. 

 

Chanikya 

danilang
19 - Altair
19 - Altair

Hi @chanikya 

 

For your case, do you need to know which field the match was found in, or only the row it was found in?

 

Dan

chanikya
6 - Meteoroid

Hi @

 

My bad! May be I made it confusing saying multiple fields. I meant that there will be other fields in the file along with the comments fields. But to answer your question there will be only one comment field i will be looking for so I just need to know the row it is found.

 

I will be required to produce a report for all the comments and show if the words present in  the lookup table are present in the comment. If there are multiple words from the lookup table in the comment I should show them all.

 

Chanikya

estherb47
15 - Aurora
15 - Aurora

Hi @chanikya 

 

What about this approach?

 

You can append all of the words to each sentence, and then calculate if there is a match using Contains. I tagged all containing with the number 1. Filter out the 1s, and use Summarize to concatenate the list of matching words. 

image.png

Let me know if this works.

 

Cheers!

Esther

danilang
19 - Altair
19 - Altair

@chanikya 

 

You may want to consider investigating the process I developed.  It's simple but very fast to match single words.  I ran the text of War and Peace, 52,000 lines. After the split to rows there were 566K words. I matched this against the Oxford 3000 look up list.(3300 entries).  The process ran in less than 3 seconds, finding matches on 50000 rows.

 

Dan

chanikya
6 - Meteoroid

Thank you @danilang. Your solution works perfect for single but I do have some phrases in the lookup tables  so the solution from @EstherB47 works perfectly even for that. I appreciate your help.

 

Chanikya 

chanikya
6 - Meteoroid

Thank you @EstherB47. This solution works fine even for phrases as well as single words. I will repost if I run into issues. Appreciate all your help!

 

Chanikya

Labels