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