Word Association Between Two Alteryx Data Connections

Question

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 · Answer

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.

Word assosiation with iterative macro.yxzp

DavidP · Answer

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

Word assosiation.yxmd

datamodeling101 · Answer

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_NameACEBUTOLOLACETAMINOPHENACETYLSALICYLIC ACIDCAFFEINECOLISTINHYDROCORTISONENEOMYCINTHONZONIUM BROMIDEBROMIDEThen my data table contains:Ingredient_NameACETAMINOPHENACETYLSALICYLIC ACID CAFFEINEACEBUTOLOL ACETYLSALICYLIC ACIDCOLISTIN HYDROCORTISONE NEOMYCIN THONZONIUM BROMIDEBROMIDEThen 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