Recursive Join Hierarchy Data Macro

Question

I am working on a flat Bill of Material data set (left to right) that can be from 2 levels to 12 levels (currently).

Level 1 is a SQL call to the database and contains a field that defines the # of Levels of the BOM.

Note - the db I am using does not allow for recursive CTE or this would have been solved via SQL.

Level 2 for each is a unique join to Level 1 and not considered for the macro.

Level 3 - 12 are identical. Level 2 joins to Level 3, Level 3 to Level 4, Level 4 to Level 5, etc.

I thought initially the Iterative Macro would be a good option, but all of the tools would need to updated.

Never building an Iterative macro before and never knew adding a Control Parameter tool automatically changed it to a batch macro until after the fact.

Although the current workflow (very top image) is quick (Total run time is 1min 30 seconds) there are 3 issues

1) Each part is run through all 12 levels.

2) Not dynamic - what if the Levels exceed 12 (in the future)

3) There is a summarization record at the end of each BOM that I do not want. Example a 5 level BOM actually has 6 levels so it needs to stop before the 6th join.

*Note a dynamic select at the end could solve #3 above.

A batch macro could be built to include a detour at each level 3-12

This would solve issue 1 & 3

Not looking specifically for an "answer" per se, but how you did or would approach a scenario like this.

Puff

Below are some hideous examples I was playing with:

stapuff106 · Answer

@Treyson I appreciate your response back and willingness to roll up your sleeves, but really appreciate you dropping knowledge.

In the example of  "There is a summarization record at the end of each BOM that I do not want. Example a 5 level BOM actually has 6 levels so it needs to stop before the 6th join." in the Level 1 SQL call there is a field that specifically states the Level, which in this case would be 5.

LVL1_KALST
5

When I read your response and you mentioned a nested Batch Macro... I was like hell[o] no.... why didn't I think of that. Head stuck in the sand, not thinking of the complete approach through and really the reason for the post.

I will put a workable example of the current data together and take you up on your offer.

Thanks again,

Puff

Treyson · Answer

@stapuff106 This is my favorite question that I have seen on the community in a long, long time. Macros are awesome and sometimes the hardest part is figuring out that you need a macro or you have a "macro problem". I am going to start off with adding some reading material. Blog articles I wrote a few years back that talk about iterative and batch macros, where to use them and why they are different.

https://community.alteryx.com/t5/Engine-Works/What-the-Heck-Is-a-Macro/ba-p/406214

https://community.alteryx.com/t5/Engine-Works/Batches-Be-Solvin/ba-p/444205

https://community.alteryx.com/t5/Engine-Works/Hello-Iterative-Macro-My-Old-Friend/ba-p/420308

If you are still with me, let's talk about your problem!

this statement here is awesome:

Although the current workflow (very top image) is quick (Total run time is 1min 30 seconds) there are 3 issues

1) Each part is run through all 12 levels.

2) Not dynamic - what if the Levels exceed 12 (in the future)

3) There is a summarization record at the end of each BOM that I do not want. Example a 5 level BOM actually has 6 levels so it needs to stop before the 6th join.

You understand the issue, we just really need to talk architecture.

I am trying to figure out in my head if we need to have an iterative macro with a batch macro inside (likely) or there is some data point that will help us remove records logically (speaking specifically to this: There is a summarization record at the end of each BOM that I do not want. thought I may be reading it incorrectly).

you were on the right path, but this part (I think) would want to be a batch macro inside of your iterative macro.

Also when you have an iterative macro there is a new workflow constant added, that might be useful to us.

So that was a lot of word vomit, not at all solving your problem, but speaking to the fact that: yes, we can solve this! I would love to help you with it, if you are interested. feel free to send a direct message and we can walk through some of the nitty gritty!