Creating list of Key Words

[Robert31395]

I am trying to do the following:

I have a table #1 that has a list of key words

I have another table #2 that has a list of descriptions

In Table #2, I would like to create a new field that has the concatenation of any of the key words (with a separator such a comma) that match the description of that record

I was able to create something that creates a Boolean field that indicates that one or more key words match, I am just trying to take it a step further and create a list of the individual key words that matched.

[Thableaus]

Hi @Robert31395 

Could you give us a sample of your input data and what your desired output (table) should be?

This makes the life of Community users much easier to help you!

Thanks,

[danilang]

Hi @Robert31395 

You can probably use something like this for the basic case

The idea is to take your input and split it into words.  From there, you join the split words to your key word list and the summarize the results to create you comma delimited list.  Join this back to your original input and use a union to include the rows that didn't match any of the keywords, resulting in 

Note that this technique only works with single words in the key word list and only matches the complete word.  The key word list I have has "description" in it, but line 3 contains the word "descriptions"(with an "s") so they don't match.  There are ways to get around these limitations, so if you need more functionality than this, follow @Thableaus advice and include an exhaustive list of requirements and sample data.  

Dan

[Robert31395]

Thank you for your reply.  I have attached a sample workflow with a sample text input for the key word table and the table to match it against (Item Description). The key word file could be single words or it could be short phrases.

The output should be the records in the item description table with another field containing the key word(s) that matched separated by a comma if applicable.

[danilang]

Hi @Robert31395 

Since your list contains phrases, the 1st workflow won't work.  This batch macro based solution should do the trick

Main

The main program sorts the key words and adds the record to the Descriptions before passing them to the parameter and data inputs of the macro.  On exit, the results are concatenated and joined to the input and the Union tool brings in any unmatched records

Macro 

The macro loops through the input records once for every item in the control parameter(phrases).  On each iteration, the action tools update the filter and formula tools with the current phrase.  The results of each iteration are unioned together and sent to the output.

The results of the process are as follows

Dan

[Robert31395]

Dan,

Thank you so much for your solution. I am relatively new to using macros so could you provide me a little more help. I downloaded the attached yxzp file but when I try and open it, it tells me that the file selected is an Alteryx Package. I chose Yes to load it. When I click on Import, it asks if I want to overwrite files. I click Yes and then I get an error message that the workflow was created by a more recent version of Alteryx and could not be loaded. When I check for available updates by going to Help and then Check for Updates, I get a message that my Alteryx is up to date. Is there something I am missing?

Thank You

[Robert31395]

Dan,

I was able to get the two individual files, The KeyPhraseFinder.yxmc and the Sample Workflow.yxmd

When I open the Sample Workflow, however, I get a message on the Phrase Finder that the entry point is invalid and that there is a missing incoming connection for the summarize tool.

[danilang · Accepted solution]

Hi @Robert31395 

Open both KeyPhraseFinder.yxmc and the Sample Workflow.yxmd in Notepad and change the second line in the XML to match your version of Alteryx.   <AlteryxDocument yxmdVer="2019.2">  change the bold text

Dan

[hellyars]

@danilang  Love the solution. Question.  How do you modify the match to capture only whole words?  My test case has "dog", "cat", and "thermal management" embedded in ipsum lorem text.  One of my sample records accidentally matched the 3 letter sequence "cat"  in the impsum lorem text here:  "Excepteur sint occaecat cupidatat no"  How can I modify to ignore non-whole words?

[imransayed]

Thanks you so much for the macro based workflow. I was struggling to create a workflow and your suggestion help me to acheive the goal.