How to split data based on column
- Subscribe to RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Mute
- Printer Friendly Page
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
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
- Labels:
- Workflow
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
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.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
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.
 
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
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
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
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
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
@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.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
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.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
@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.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
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.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
@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.)
