Calculate Sub Totals and Output separate excel files grouped by country

Hey all,

I am looking for any support on a workflow calculating sub totals and outputting separate excel files by country.

My input is an excel file which is supposed to be separated by country. In addition, the output excel file is supposed to contain sub totals in the last row. I hope this image illustrates my workflow I am looking for:

Approach:

1. Calculate sub totals for each country

2. Output separate excel files for each country expanded by a final row containing the sub totals

Problem:

- Calculation of sub totals works fine BUT finally it generates two separate files for each country. 1st file contains dataset (e.g. "China") and the 2nd file only contains the sub totals (e.g. "China Total").

I know that this problem arises because my title for the sub totals are called e.g. "China Total" and listed in the column "Country". So, Alteryx recognizes "China" and "China Total" as different countries and generates separate output excel files.

Actually, the files "China" and "China Total" are supposed to be in one file.

Please find my workflow and a sample file in the attachment.

I am thankful for any support!

sample.yxmd

Sample all.xlsx

Accepted answers

grazitti_sapna

@schoeneborn-hendrik , is this what you are looking for?

Thanks.

Sample all.xlsx

sample (3).yxmd

All comments

grazitti_sapna

Hi @schoeneborn-hendrik , I am not sure if this is a best approach but as per your requirement I have made a minor change if it is feasible for you. Please refer to the screenshot.

In this screenshot, I have replaced your forumla tool where it was hardcoding the country with total eg: China Total due to which it was being considered into a separate file. Now I have changed the logic to

if IsNull([Business Sponsor]) then [Country]+"Total" else [Business Sponsor] endif

and used it after the first union tool in order to achieve the desired output.

Also attaching the workflow along with this post for better understanding.

I hope this helps.

Thanks.

sample_GS.yxzp

schoeneborn-hendrik

Hi @grazitti_sapna ,

thanks for your immediate response.

Since you have a more recent version of Alteryx, I am not able to open the workflow. My version is 2019.3.5. Are you able to share it in a compatible way for me?

Thank you a lot in advance!

Quick Links

This months top contributors

atcodedog05 19598

Qiu 15878

binu_acs 15708

MarqueeCrew 13708

apathetichell 13703