How to split data based on column

[SahadhKuruniyan]

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

[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.

 

[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.

[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.)