community
cancel
Showing results for 
Search instead for 
Did you mean: 

Alteryx designer Discussions

Find answers, ask questions, and share expertise about Alteryx Designer.
Upgrade Alteryx Designer in 10 Steps

Debating whether or not to upgrade to the latest version of Alteryx Designer?

LEARN MORE

Look for 1-N Colors in Text, Match to List of Colors and Append Names of Color(s)

Comet

I think this is a simple one. 

 

I have a body of text.  It may contain the name of a color 0-N times.  I want to use a lookup table to find the name of a color or colors in the body of text and append the name of the color.  In the attached sample workflow, I use a Find/Replace tool set to append.  

 

Here is my problem.  The Find/Replace tool only appends the first match.  It does not output any matches beyond the first.  How do I account for the names of multiple colors?

 

(The real world list of "colors" is approximately 150 entries.  And while the number of colors in the body of text can be 0, that is the exception.)

 

 

Magnetar

Hello @hellyars 

 

The procedure here is to split each body into individual words, find the all colors, cross tab them back into one color row per body and join back to the original text.  The key to this is adding a recordID at the start to be able to match each individual word and color list back to its original body.

 

Solution.png

 

Dan

 

 

Comet

@danilang 

Thank you.   I failed to account for 2-part names in my example. The real world data uses names of countries not colors.  

 

How can I account for 2-part country names such as the United Kingdom or even 3-part names like the United Arab Emirates?

 

If all else fails, I could perform a second scrub of the whole text for these handful of unique 2 and 3-part names.

 

 

 

Magnetar

Hi @hellyars 

 

Add a second column to your lookup table that maps multi-word countries to single words.  

 

Edit: include all countries in this list, even if they have single word names.  Add the single names to fixedNames column as well.

 

FixedCountry.png

 

Use an initial Find Replace to scrub the input data.  Continue with the original  work flow using the single names and finally replace the single word names with the multi-word ones

 

Solution v2.png

 

 

Result

 

Result.png

 

Dan 

Labels