Batch Macro to Filter and Output Dynamically

[lsalmon33]

I apologize in advance if this has been asked, I've seen a number of similar posts with nothing that answered my specific questions. Feel free to direct me to another thread if this has been answered.

I'm trying to generate multiple excel output files that will have different columns based on a crosstab of the name column. I've been working on a batch macro, but I'm still pretty new to building my own macros.

Ultimately I have a workflow that generates over 800,000 rows by the point I want the Macro to come in and filter the data based on "File Name", crosstab with Group on "Weight", new columns are "Name", and the values will be the sum of the "Rate" column and output to individual excel files named the "File Name." And then do that process again for each unique File Name.

I'm attaching a sample of what the data looks like before the macro and what the outputs should look like.

The best I got was a macro that would do the whole process for just one row of data, it didn't even filter everything with the same File Name.

[lsalmon33]

I was able to use a variation of the macro built by Claje in this thread to figure it out. https://community.alteryx.com/t5/Alteryx-Designer-Discussions/Dynamic-Filter-in-Batch-Macro/td-p/314... 

[Scott_Snowman]

You also don't need to use a batch macro for this use case. You can adjust a File Output tool to use a field value as the name of the file, and then if there are multiple values of the field within the data, the File Output tool automatically creates a new file for each group of values in whichever field you select.

So it's sufficient to crosstab the data such that you have two rows for "pis" and two rows for "smppg", ensure that the rows keep the column "File Name" which has either "pis" / "smppg", and then use the File Output tool with "Change File / Table Name" as an option. Make sure to uncheck the box which keeps that column in the data so the file name isn't part of the output itself.

[lsalmon33]

Thanks for the suggestion @Scott_Snowman! That is really good idea. The only problem with this is that each of my files have different output columns. Is there a way to remove empty columns on the output as well?

Essentially I'm creating 800 different files based on the File Name column, and within those 800 files there are about 15 variations of columns. Worst case I could run the workflow with your suggestion once for each specific output orientation.

[Scott_Snowman]

 Ah, no @lsalmon33 you are correct. This would not really be within the scope of just the File Output tool alone - apologies for the false start there! Definitely would need a batch macro to handle different groups of records with different field arrangements.