How to split data based on column

Hi Team,

I need help to split data based on column A. My data looks like:

Country	Size	Code
Brazil	10	A
Brazil	23	B
India	11	C
India	23	D
Gemrany	15	E
Gemrany	53	F
UK	12	G
Brazil	32	H

I want to split data like below and do further calculation in workflow:

Country	Size	Code
UK	12	G

Country	Size	Code
Gemrany	15	E
Gemrany	53	F

Country	Size	Code
India	11	C
India	23	D

Country	Size	Code
Brazil	10	A
Brazil	23	B
Brazil	32	H

Can any one please help

Workflow

Accepted answers

All comments

JosephSerpis

Hi @SahadhKuruniyan if you want to spilt your data as you describe you would need to use a batch macro on your country column which will spilt the data as you describe.

jdminton

Depending on how you use the data, you may also be able to use the output tool. The settings below will each country to a separate sheet in Excel. If your end goal is Excel, this is a simple solution to add at the end of the workflow as there are other ways to do the calculations in the workflow by country. You could also use this method to export and bring data back into another workflow (or the same if you run it twice). You can use the Runner crew macro to run the workflows sequentially or if you have version 2023.1, you can use control containers to read the Excel back in after creating to do further calculations.

Breakout Countries.yxmd

SahadhKuruniyan

thanks for the information, much appreciated. However my will not end here, I would need to make further updates in data after this.

Regards,

Sahadh

SahadhKuruniyan

Thanks Joseph for the infromation, However I am pretty new to alteryx, can you please help me with some articles or video so I can refer and prepare batch macro

jdminton

@SahadhKuruniyan The second part of my comment might not have been clear. Here is an attached workflow for that example. It makes sense that you would be able to split the records into groups dynamically without having to add a bunch of filters, but if you are trying to do different processes to each after the split, a batch macro wouldn't work. The end result would keep the data in the same dataset.

The workflow below does what you need. It uses csv (smaller than Excel) and runs the outputs and brings back in as inputs. You would need to manually create the inputs though if any new countries appeared. I've also added a test of names to identify new countries added that might be missing. Let me know if that helps.

Breakout Countries.yxmd

JosephSerpis

Hi @SahadhKuruniyan have a look at the Alteryx academy if you want to find out more about macros. A good start would be the Interactive lessons.

jpaplanus

@jdminton, I just wanted to thumbs up this idea and leave a comment that this was the solution for a specific need I had. Something I had not needed to do before and was exactly what I was looking for Alteryx to do. Needed to create 244 different worksheets based on an ID field. Would have taken a person possibly hours, but was done with this solution in less than 2 minutes.

PARESH

Hi,

What if I don't want data in output but process further within workflow instead of getting it in output file. Separate filter and then check within workflow.

jdminton

@PARESH There are several ways to continue processing the data. Typically, you wouldn't need to split the data until output as there are formula tools (multi and not), joins, etc. that you can use to perform different processes on the data while it's still in the same set. Do you have something specific you are trying to do? Maybe create a separate post so it gets the attention needed. (Feel free to copy the link here, so I get a notification.)

Jodie_sam

Hi jdminton,

Thanks so much for the solution example. It worked absolutely brilliant and I got the files split by country like how I wanted.

Quick Links

This months top contributors

atcodedog05 19598

Qiu 15867

binu_acs 15708

MarqueeCrew 13708

apathetichell 13703