Converting a Batch Macro into an Iterative Macro

[iCFO]

I just built an extensive Batch Macro (nested within another Batch Macro) with 36 Control Parameters that will Dynamically Filter / Apply Formula Changes based on settings from an Excel Controller. I thought that I could use a Union Tool or GroupBy the Batch ID in order to get it to loop the records through for the next batch, but unfortunately I can only output duplicate records for each batch or filter for just the changed records for each batch... I am coming to the unfortunate realization that it should have been built as an Iterative Macro in order to loop records through to be assessed for further changes.

I am really hoping that there is some cool trick for Iterating a Batch Macro, or passing formula tool actions into an Iterative Macro (if it is nested in a Batch Macro) that would allow me to convert this over to an Iterative Macro? The only way I have been able to get a formula into an Iterative Macro for evaluation so far is when I created an Iterative version of the Dynamic Replace Tool (which has a basic formula Eval built in). While I am on the topic... Why don't we also have Control Parameters in Iterative Macros in the first place?

[danilang · Accepted solution]

Hi @iCFO 

There are two basic criteria for determining whether to use a batch or an iterative macro. 

1. Do you need to to modify the Input rows and use the modified values as input to the next iteration?

2. Is the number of iterations unknown at the start of the macro and only determined in the course of an iteration?

If you answer Yes to either of these then you need to use an iterative macro.  If the answer to both is No, then you can use either type, but a Batch macro will probably be easier to configure.

Unfortunately, there is no magic bullet to convert from a batch to an iterative macro, but you can simulate a control parameter in an interactive macro.   Add another Macro Input and pass in a dataset that has a RecordID field, i.e. starts at 0 with an increment of 1, as well as all the control parameter fields.  Use a filter after this input with [RecordID] = [Engine.IterationNumber] to use the row that corresponds to the current iteration.  

For your situation, you should probably look at converting your outer macro to an iterative one that calls the inner batch macro passing the the row mentioned above as the control parameters.  The output of you inner macro goes to the iterative Output of the outer one so the modified Input records can be used on the next iteration, with the control parameters that correspond to row of the second Input.  

To stop looping, check to see if the result of the filter on the second input has 0 rows.  If so, you've looped through all the "Control" records so pass the output of the inner macro to data output and no rows to the iterative output.

Dan

[iCFO]

The answer to Criteria Question #1 is a big yes.

Converting the outer macro would be much easier than converting the inner macro. The outer macro is quite simple and is really just doing some controller data transformation to break back out incoming concatenated values so that I reduce the number of Macro Field settings from 36 to 5. I will try to implement your recommendations and let you know how it goes. Thank you very much for your quick and easily understood response!

This was very illuminating:

"The output of you inner macro goes to the iterative Output of the outer one so the modified Input records can be used on the next iteration, with the control parameters that correspond to row of the second Input."

I was definitely envisioning the flow of data incorrectly. I had assumed that the inner macro would cycle through its own input / output until completion (Unless it was waiting for the outer macro to batch send control parameter data to an input.) I now understand how an iterative outer macro could solve the issue.

[iCFO]

That did it!

Thanks a ton for the guidance @danilang !