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! TestWorkflow.yxmd

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

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!

TestWorkflow.yxmd

Workflow

Macros

Transformation

Batch Macro

Iterative Macro

Accepted answers

SPetrie

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.

UpdatedTestWorkflow.yxmd

parth_t

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

All comments

SPetrie

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

UpdatedTestWorkflow.yxmd

parth_t

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

UpdatedTestWorkflow.yxzp

vkiechlin

Hey! I know this is an older post from last year but Im in a similar situation as the one who posted this last year. Im trying to get the hang of using a macro on this workflow but Ive only made a couple of them in the past few months and im not great at them, but I think that they would really be what id need to use here.

Im basically trying to take a large number of columns ~200 -> Have it filter on more like 10-20 columns (On excel sheet 1) -> Have each column sorted, then quartiled -> Then have then same excel sheet 1 (which has weights on it) multiply the weight and that quartile from each column before adding them all together to get a final "complete ranking"

Im imagining its a very similar process to this setup and i tried to recreate it based on what was posted here but I cant seem to get the macro to work correctly. Any help is greatly appreciated and let me know if you need anything from me here!

Sample Data 1.xlsx

SPetrie

@vkiechlin I think it will be best to create your own post to ask this.

I would also suggest adding a few more attachments to make it easier to assist.

Preferable, an example workflow with your example inputs and your current macro along with with expected output file will help greatly.

Quick Links

This months top contributors

AkimasaKajitani 389

mceleavey 388

mbarone 337

Hollingsworth 335

LanisC 335