batch macro where each iteration does not include data from the prior iteration

[Paulo1300]

Hi All,

I have a batch macro where I basically made a dynamic join. On each batch I have a parameter connected to a formula that acts as the column that I will join to, so on each batch iteration, I get a different join.

The final output leads to all of the joins unioned together.. and due to this, sometimes there are duplicates if a prior batch already joined on that same row of data. 

So I am just wondering if there is a way to prevent a row of data from passing through on the next iteration, so that there are no duplicates.

Below is a screenshot of my macro:

So really what I am looking for is kind of like an iterative macro and a batch macro combined. 

Some ways I could do it is to output each iterations results as a yxdb and add a join right at the beginning of the workflow that filters any rows that already exist in the output.. but.. i would prefer to be able to do it without using an outside data source (similar to how iterative macros work)

Let me know if you have any thoughts!

[SPetrie]

Is that control parameter numeric?

If its going through 0,1,2,etc., you could swtich to an iterative macro and use a filter tool using engine.iterationnumber to limit the records.

Are you able to share the macro and some dummy data to test with?

[Paulo1300]

I attached my workflow and macro with dummy data.

The main workflow dynamically makes the batch scenarios and ranks/orders each scenario, then the batch macro processes each scenario.

For more clarity on what I am trying to do, I am basically trying to be able to have an input of X columns (can be whatever number of columns) where I only join on the columns that arent empty for a given row. So basically I can make a parameter sheet where I can say "if it is this item, join this value.. if it is this item facility, join this value.. if its this vendor, join this value.. etc" without having to join a bunch of times or have different lookup tables for each scenario. I want to use this in a lot of workflows where my parameter files can have different columns, so I cant just hardcode the joins, it needs to be based on whatever columns are in the inputs.

I don't know how I would do it with an iterative macro because I cannot "change" the join I am trying to do. Each time it joins on a different "join key" that is driven by the batch macro's parameter

[SPetrie · Accepted solution]

Give this a shot.

I converted it to an iterative macro instead of a batch. Its using the group number of the formulas with the engine iteration number to act as the batching mechanism.

It will pass the whole dataset from L to the macro initially. It will then take the groups with their formulas and filter to just the current engine iteration number +1 (since it starts at 0) and apply that formula to each row using the dynamic replace tool.

It then goes through the join you had setup already. The items from the left that dont join will be passed to the iteration output to be run through again with the next group of formulas and so on until all formula groups are exhausted. 

At least with your test data, this macro matches your expected unique output.

[Paulo1300]

Thank you!! I will check it out

[Paulo1300]

This works great! Thank you!

I didnt even know a dynamic replace was a thing, that is awesome