Upgrading Server to version 22.1? We’ve enabled the faster AMP engine by default. Read about how these system settings changes might affect you.

We’re aware of an intermittent issue with our My Alteryx login and are actively working to have the issue corrected. If you run into an error when logging in, please try clearing all cookies or accessing the community on a different browser. Thank you for your patience!

Alteryx Designer Discussions

Find answers, ask questions, and share expertise about Alteryx Designer.
SOLVED

Iterative Macro Tree Join - Requesting Help

nbrightwell
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.


4 REPLIES 4
DawnDuong
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?

 

Dawn.

nbrightwell
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.

DawnDuong
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.

Cheers

Dawn

nbrightwell
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!

Labels