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!
Solved! Go to Solution.
Hi @chanikya
Try this technique.
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
Dan
Hello @danilang
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
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
Hi @danilang
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
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.
Let me know if this works.
Cheers!
Esther
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
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
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