Looking for Starter Kits? Head to the Community Gallery! Now formatted as YXIs for easy installation.

Alteryx Designer Desktop Discussions

Find answers, ask questions, and share expertise about Alteryx Designer Desktop and Intelligence Suite.

Iterative Macro Tree Join - Requesting Help

5 - Atom

Hey everyone,

I have a list of items I can make and the ingredients it takes to make them. In a large kitchen, some of the ingredients fit for multiple recipes. As an example. Both cookies and cake use sugar. One baker may take sugar from the cookies section for their cake. I need to know which recipes have ANY similar ingredients and group them together in a new field. Example. Because cookies and cake use sugar, they are now in group 1. Cookies also chocolate chips while cake doesn't. Cookies and cake will both still be group 1. But now, chocolate fondue, comprised of chocolate chips, now will also be group 1. However, hotdogs, share no similar ingredients. Hotdogs will then be a new group (group 2). Hotdogs would similarly check its ingredients against all ingredients within the list. This would repeat until the entire process has grouped every recipe ingredient.

I have played with this as a macro so far. But I am missing something. I have attached a starting place, with some example steps on how this process should roughly work.

13 - Pulsar
13 - Pulsar

hi @nbrightwell 

Suppose you have:

  • Cake: Sugar, flour
  • Juice: water, Sugar
  • Brownie: Sugar, chocolate 

How would the grouping logic works: i.e. Cake and Juice grouped together or Cake and brownie grouped together?



5 - Atom

Hi Dawn,

Excellent Question! Because they all share sugar, they would all be grouped together. 

So to continue that thought. Because Juice has water as an ingredient. Lets say soup was also on this list. Soup would then be grouped with Cake, Juice, and Brownie.

13 - Pulsar
13 - Pulsar

Hi @nbrightwell 

thanks for the additional details. This looks like an exercise for the Alteryx course / program. Maybe you give it a first shot to go as far as possible and then fellow community members can help where you encounter a blockage?

some hints:

1) one possible non-macro method may involve the Make Group Tool, Tile Tool and Join Tool.

2) this can also be solved via iterative macro. A possible schematics can be: starting with iteration 1 first assign any random food to group number nth with n being the number of iteration. Second, use Appropriate Join setting to find any other group that has at least one common ingredient with this group. Third, those foods that are found to have common ingredient are also assigned to group nth. Those that have no matching ingredients are loop back as input to the next iteration. Done till no more left over.



5 - Atom

I got it to work. But I am sure there is more efficiency to be had. One particular problem I am having, I want to max a maximum amount of iterations, but find a way to prevent the data from continuing to run once I have already finalized all of the data. But I need the full table set for each run until is finalized. So I can't filter out rows as I go, it has to be the full table.

Let me know what you all think!