Split the data and generate dynamic sheets

[RichardAlt]

Hi Guys! The Alteryx is very new to me, So I need to practice myself. I'm creating a sample workflow and I need to split the data base on the Group. 

This is the Group List,

IDBU

MYMIB

IDMAM

PHBU

JPBU

HKRO

SGRO

HKBU

HKMAM

MITDC

This is not the all the Groups, Sometimes the Group has a "SGBU" sometimes has not. All the Group is dynamic. For ex. In Day 1 the file 1 has a Group "MYMIB", "HKRO" .. etc, But in Day 2 has not. 

I need to split the data base on the Group if there is 10 Unique groups so there is must be also 10 sheets. 

But I need dynamic workflow.

I made a sample workflow but the output is not correct, No Headers, No first row, No Total each group.

Kindly check the attached files,

The "Output.xlsx" file is my desired output, The "Alteryx Output.xlsx" is my alteryx output, the Sample is my workflow.  And the "Namelist.xlsx" is my source file.

Sorry for my bad English. Thank you!

[DavidP · Accepted solution]

Hi @RichardAlt, welcome to the Community and well done on trying out the reporting tools!

I've amended your workflow a bit and added a few notations to show what every bit does. Please have a look and let me know if you have any questions.

[RichardAlt]

@DavidP Thank you! It Works, But I have another question, There is multiple column of Amount? I've attached the updated source file.

And for the Header Row 1 "Name List 2020" i have that text, How can I add the group, What I want is something like this, "Name List 2020 of HKBU".

[DavidP · Accepted solution]

Hi @RichardAlt,

Please see updated workflow attached. Did you mean Salary and Salary2 when you mentioned multiple columns of Amount?

If that is correct, you can just add them into the Summarize tool to get subtotals for them as well.

I updated the Table tool to include Column Names.

In the Report Text tool, you can insert dynamic data from fields in the text by selecting them from the Available fields dropdown, so that way you can easily add the Group name to the title of each Table.

[RichardAlt]

How can I remove the group in last row?

Even in the summary row there is group name.

Thank you!

[DavidP]

We need to Group name in the data to correctly associate each summary row with the correct set of data when the tables are built.

So if you want to remove the Group Name from the Table in the Summary row, you can create a Column rule in the Table tool, like this.

Updated workflow attached.