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

Creating list of Key Words

Robert31395
7 - Meteor

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.

 

 

9 REPLIES 9
Thableaus
17 - Castor
17 - Castor

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
19 - Altair
19 - Altair

Hi @Robert31395 

 

You can probably use something like this for the basic case

 

W.png

 

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 

 

r.png

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
7 - Meteor

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
19 - Altair
19 - Altair

Hi @Robert31395 

 

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

 

MainMain

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

 

MacroMacro 

 

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

 

R2.png

 

Dan

Robert31395
7 - Meteor

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
7 - Meteor

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
19 - Altair
19 - Altair

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
13 - Pulsar

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

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. 

Labels