Trouble adding specific fields on multiple excel files

Question

I am having trouble creating a workflow to sum together multiple fields recording the sum on an output file and repeating this process for multiple excel files. The excel files have the same schema and tab names. I have no issue creating a workflow to identify the fields and add them together, I just cannot get alteryx to do the same process on all files in my directory. I have provided more detail below

To start we have 3 excel files and we want to add a number of fields on the tab named SAL(this tab exists on all 3 sheets):

File 1$SAL

File 2$SAL

File 3$SAL

The output file should look like:

FIle 1$SAL - summed amount

FIle 2$SAL - summed amount

FIle 3$SAL - summed amount

I cannot be more specific because I am working with proprietary info, but I have attached a screenshot that I will explain below...

I started with an input data tool to pull in all files starting with "WB." 
In the top row - I used the select tool to select 2 columns(the label name and values columns).
then I used a filter tool to filter out values I intend to sum. Lets call it "Shares"

then I used a summarize tool to add all values with the label name shares

In the bottom row - I used a select tool to select the column containing the file name

then I put a filter to filter any extra info so I am left with only the name

Then I used a join tool to combine my file name with the values I summed.

Then I used an output tool to create a new excel file displaying the file name and summed values

I'd like to repeat this entire process so my output file contains the summed values for every file in my directory.

workflow screenshot.PNG

dreldrel · Answer

Just wondering, is there any update to this topic?

KGT · Answer

So, your output is just Filename|Total, as in 2 columns?

The process looks pretty solid, but what's the error? Is it an error, or are you looking for advice on how to build it?

The "simple" solution is to put the top stream into a batch macro and then feed in a full list of files with sheet names as the control parameter. That would most likely involve:

* Directory Tool
* Macro to get sheet names for all files (a few of these floating around on the community)
* Formula to construct the qualified name (Filename.xlsx|||Sheetname)
* Batch Macro that does your operation

However, you might also want to take a look at the following links:

* https://community.alteryx.com/t5/Engine-Works/The-Ultimate-Alteryx-Holiday-gift-of-2015-Read-ALL-Excel-Macro/ba-p/9988
* https://community.alteryx.com/t5/Alteryx-Designer-Knowledge-Base/The-Ultimate-Input-Data-Flowchart/ta-p/20480

Mark_Sneed · Answer

Hello Calvin,

Thank you for responding. I edited my post with more info. Please note, because I am working with proprietary info, I cannot post exactly what I did so I gave a summary.