Creating a Batch Macro for Multi-Column Data Processing and Aggregation in Alteryx

[parth_t]

Hey Alteryx community!

I appreciate any suggestion, solutions and answers in advance.

I have a workflow to process multiple column - Transform (Group and aggregate), Cross Tab (Group category, status as column header and aggregate as values for new columns). I need to do this for multiple columns, in the test workflow attached I have done this for 4 columns. Whereas in the actual dataset there are many more columns, and the column list could expand. Further I filter each category to remove null values before appending all four summaries into one dataset.

• Please see the attached workflow for additional context.

I have started learning macros, and I am pretty new to them. I was wondering if there is a potential solution to achieve this using batch or iterative macro as the nature of this process is repetitive. 

One potential solution I found to create a text input list with the names of all of the columns that needs to be processed through the macro. But I keep hitting roadblocks due to lack of experience with macros. 

Any advise would be great!

[SPetrie · Accepted solution]

You can get the same solution without a batch macro, although it is able to be solved with one. The easier method is to transpose your data before summarizing it so that everything is in a singular column.

We can workup a batch macro if you want to play with one, but I think simpler is better in this case.

[parth_t · Accepted solution]

Hey, This is fantastic! Simple solutions are always the best ones. It also takes in account for any additional columns added to the dataset. 

If you have time to spare I would love to see how the same could be achieved using batch macro. Often times such solutions can be reused in different instances.

In any case, I appreciate your quick response and solution :)

[SPetrie]

Glad I could help!

There are multiple ways you can convert this to a macro and achieve the result.

If I was to try to duplicate your original process into the macro I would go about it like this.

Use a select tool to remove the columns that will NOT be acted upon and use a field info tool. That gives us a list of the columns we DO want to act upon. We are going to feed that to our batch macro control parameter and set the name column as the field we want to work with.

Inside the macro it will be nearly identical to one of your previous branches.

I use a formula tool to create a new column called BatchTestCol and set it to [Column A] in the tool. The action tool is set to remove Column A from that formula and replace it with one of the names that come in from the control parameter.

The summarize and crosstab tools are setup the same was as you had it before, but its using the BatchTestCol instead of the original column. Once done, I rename it and send it to output.

It repeats these steps for each column name passed to the control parameter and the output automatically unions the results together.

Again, that is just one way to do it, but hopefully it helps spark additional ideas :)