How do I stop Macro producing duplicate rows?

I have a Macro solution to an issue I was facing: Outputting to an excel file from a workflow and then re-inputting that output in the same workflow.

The solution involved (read somewhere on the community) creating a batch macro that simply took the input file in. I connected this with a 'block until done' tool and hey presto it worked. However, there are 833 rows in the input file and the batch macro is looking at each row x 833! So i end up getting 693k rows once the macro is complete.

This is going to become a big issue as the original output file will get larger over time, thereby increasing the final output exponentially and significantly damaging performance.

The question is: How can I stop my batch macro from going through each row and instead just give me the whole data set?

Here is part of the workflow:

Workflow

Here is the Macro:

Macro Macro

Macros

Accepted answers

Christina_H

The simplest way is probably to add a Count Records tool from the Transform palette, it doesn't need any configuration. Drop it between your Block Until Done tool and the macro.

There are other suggestions in this thread if you're looking for a different solution:

https://community.alteryx.com/t5/Alteryx-Designer-Discussions/Delay-an-Input-Data-Tool/td-p/10241

All comments

Ben_H

Hi @jbmcole87,

A batch macro will run once for every row connected to the macro input.

If you only want it to run once, it can only have a single row connected as the control parameter.

Regards,

Ben

Christina_H

I don't know about your specific case, but I've used some batch macros and you need to feed in how you want the data batched. Since you don't have any other inputs in your macro, I think you're feeding in your 833 lines of data as the batches. Try using e.g. a summarize tool to feed in only a single line of data so that the macro only runs once.

jbmcole87

I'm not too experienced with Macros, this is the first one I've created so not sure if feeding it only one line will get the result I need. To be honest, if there's a better way to delay the input until the first output is done then that's all that matters.

Christina_H

Can you split the process into two workflows instead, and run then sequentially? E.g. use the List Runner tool from CrEW macros? Although I do think limiting the macro input to one line will sort it out.

jbmcole87

OK thanks - how do I limit it to one line?

Christina_H

The simplest way is probably to add a Count Records tool from the Transform palette, it doesn't need any configuration. Drop it between your Block Until Done tool and the macro.

There are other suggestions in this thread if you're looking for a different solution:

https://community.alteryx.com/t5/Alteryx-Designer-Discussions/Delay-an-Input-Data-Tool/td-p/10241

jbmcole87

Thanks that worked!

Quick Links

This months top contributors

atcodedog05 19598

Qiu 15867

binu_acs 15708

MarqueeCrew 13708

apathetichell 13703