Word Association Between Two Alteryx Data Connections

[datamodeling101]

Hello,

I have the following lookup table in my work-flow which has ingredient name, word count of the ingredients, and num_of_ingredients is always equal to 1.

Additionally, I have the data which contains a wide variety of wordcounts and number of ingredients, such as the following:

What I'm attempting to do is use the single ingredient lookup table as a search to find the exact ingredient name starting with the larger word counts and break apart the column's ingredients into separate columns for each ingredient in the number of ingredients. 

Below is my current work-flow with the filter on the end being the data set and the last formula tool being the lookup table.

How would I go about searching a field in the data table in the lookup table?

[DavidP]

Hi @datamodeling101 

If I understand the problem correctly, you can use a find replace tool to find the matches, and a summarize tool to find the matches with the largest wordcount.

Have a look at the attached example

[datamodeling101]

Hi David, 

Perhaps I explained my problem incorrectly.  But, I don't believe the find and replace tool will work for what I'm attempting to do because it's not an exact match it's the substring of a match in a string.

If my lookup table contains the following:

Ingredient_Name

ACEBUTOLOL

ACETAMINOPHEN

ACETYLSALICYLIC ACID

CAFFEINE

COLISTIN

HYDROCORTISONE

NEOMYCIN

THONZONIUM BROMIDE

BROMIDE

Then my data table contains:

Ingredient_Name

ACETAMINOPHEN

ACETYLSALICYLIC ACID CAFFEINE

ACEBUTOLOL ACETYLSALICYLIC ACID

COLISTIN HYDROCORTISONE NEOMYCIN THONZONIUM BROMIDE

BROMIDE

Then my results should look like the following:

Ingredient_Name                                       Ingredient_Name1 Igredient_Name2       Ingredient_Name3  Ingredient_Name4
ACETAMINOPHEN ACID CAFFEINE                           ACETAMINOPHEN    ACETYLSALICYLIC ACID  CAFFEINE
ACEBUTOLOL ACETYLSALICYLIC ACID                       ACEBUTOLOL       ACETYLSALICYLIC ACID 
COLISTIN HYDROCORTISONE NEOMYCIN THONZONIUM BROMIDE   COLISTIN         HYDROCORTISONE        NEOMYCIN          THONZONIUM BROMIDE
BROMIDE                                               BROMIDE

[DavidP]

Hi @datamodeling101 

The find replace tool can be used to match substrings within a larger string, as shown below

In order to solve your problem, the process needs to be iteratively repeated - this is typically done with an iterative macro. When I have a bit more time I can write one for you.

However, the principle is illustrated below by repeating the set of steps 4 times.

Example workflow attached

[DavidP]

Hi @datamodeling101 

As promised, here is the workflow built with an iterative macro. It is currently set to stop at 100 iterations.

Have a play with it and let me know if you have any questions.