Create Multiple Files - Render Tools

[KrisManns]

I have a workflow using render tools to create a multiple reports (i.e. consolidated report & by-country reports with selected entities).  Please see the attached workflow for details.  

As you can see, I am transforming the data so that country/jurisdiction is not included in the final dataset before the render tools. I am using a select tool to parse out the data needed for each report. 

I would like to find a dynamic solution to automatically transform the data and create the reports I need.  I'm not proficient with batch macros but maybe they can help here?  Any help would be appreciated!

[jrlindem]

You can use the fields that split the data into the different files using the formula tool to create the unique filepath's.  That way you can exlude the fields that are handling your partitioning yet still have the filepath drive the splices.  Configure this in the output tool and select to exclude the filepath in the output.

This avoids batch macros and still splits your data into multiple file outputs without including those fields you don't want in the output.

If that's unclear I can take a look at your workflow.  But hopefully that gets you started.  -Jay

[abacon]

@KrisManns I agree, you don't need a batch macro necessarily. The render tool and Output Data tool have functionality that will create a file for each grouping selected. Using the method @jrlindem described is what you should look into. Here is a blog that describes the functionality - https://community.alteryx.com/t5/Engine-Works/How-to-Render-to-Multiple-Named-Excel-Sheets/ba-p/1015...

Bacon

[binuacs]

@KrisManns one way of doing this

[KrisManns]

These solutions get me close but in each of the country files how do I delete the Entity columns that aren't applicable to each specific country.  Hopefully the attached screenshot demonstrates what I want the final by country output to look like 

[jrlindem]

@KrisManns I took a look at the original workflow and your screenshot.  I attached a stripped-down version of the workflow just illustrating how you can get each CompanyName/Jurisdiction on a separate row along with its adjoining data in its own table.  From there, assuming this is what you needed, you should be able to go back into layout and formatting with the goal of outputting one file per data row according to the CompanyName/Jurisdiction.

Ultimately, handling the data split earlier in the process avoids the need to CROSS-TAB the data after your TRANSPOSE and it also avoids having to repeat chunks of tools...

...and if I've completely missed your desired goal... sorry 😂

[KrisManns]

Hi Jay, I noticed you had the two US entities (Entity A & C) in separate files.  I need them combined together on one sheet in the US excel file.  I hope that makes sense

[KrisManns]

Thank you for your response.  I need the countries to be in separate files, not separate tabs..  Please also see my comments regarding the entities to include in each file.  The US should only have two entity columns.  The remaining country/jurisdiction files should only have one entity column.  

[KrisManns]

Thank you for your response.  Can you take a look at my comments to the others on this chat?  I have not figured out a solution that will capture all of my requirements.

[jrlindem]

@KrisManns - Took another crack at it.  Didn't originally see the two entries for "United States" ...whoops.  Also, I took out all your table/layout/render tools to simplify the proof-of-concept, but the essence is here (again, if I'm understanding the requirements).

The workflow is creating separate outputs (four different excel files) like this:

That end up looking like this (note the two columns for United States):

You can see that each file is showing only the "Entity _" that pertain to each Country along with the summary data.

What I'm really trying to show you is that the layout of the data is possible and that the ability to output to four different files, with different "tables" of data is possible all from one stream using formulas to build a dynamic filepath (see formula tool).  How you adapt this to suite your specific needs I'll leave up to you, but the concepts are here in the attached workflow.

If this isn't what you were looking for, I'll have to admit defeat and throw up the white flag.  Either way, best of luck and hope this helps.  -Jay