Split a stream into multiple streams based on values in a specific column

Hi- I just spoke with Alteryx support about this and they suggested I add this to the Community page. Basically, for an incoming data stream, I'd like to be able to "split" the file into unique files based on the value of a particular column. I've attached a file for reference- in this file, it would be great to be able to have the incoming data split according to the Brand or Vehicle Type fields, with each stream available for further manipulation after the split.

While this can be accomplished one at a time with the Filter tool, for large files with 50+ "split-by" values, it would be great to accomplish this in one step.

Thanks in advance for your vote!

Alteryx Sample File.xlsx

Common Use Cases

Accepted answers

TaraM

I am curious what you want to do with the files after the split. The Output tool can automatically create individual files for each value in a field.

2016-10-10 09_35_38-Output Data (2) - Configuration.png

2016-10-10 09_40_32-Alteryx Designer x64 BETA - New Workflow1_.png

You can easily use the directory tool and a dynamic input tool to read all the files back in.

So I am just trying to understand what is it exactly you are trying to do with the files that cannot be done?

All comments

RodL

Without knowing the actual business process you need, you should be able accomplish the "splitting" by creating your process in a batch macro and then running process through the list of the values one at a time.

TaraM

I am curious what you want to do with the files after the split. The Output tool can automatically create individual files for each value in a field.

2016-10-10 09_35_38-Output Data (2) - Configuration.png

2016-10-10 09_40_32-Alteryx Designer x64 BETA - New Workflow1_.png

You can easily use the directory tool and a dynamic input tool to read all the files back in.

So I am just trying to understand what is it exactly you are trying to do with the files that cannot be done?

ryan_richardson

Thanks for your prompt response. I work in healthcare and have sets of diagnosis and procedure codes that are used to categorize patients. For patients falling into different categories, there are a number of data operations that we undertake that are unique to the patients in that category-

I like your idea about writing the data out and reading the files back in- may give that a shot-

Thank you-

Ryan Richardson

TaraM

This is what I was referring to with the screenshots above.

WriteMultiFiles.yxmd

nick_schimweg

Hi Ryan,

You might find this basic macro I wrote a while ago useful (attached). If the stream has more than 5 values put another copy of the macro on the 5th output to split the stream again.

Nick

SplitOnField.yxmc

ryan_richardson

Nick- I really appreciate this- Thank you!

dgsigl2

Tara,

I came across your reply in researching a similar issue. Basically, I have a file with transactional information and I want to split out and analyze transactions based on the bank of origin. I created the Temp Output, pointed the Directory input toward the temp file, but am having trouble configuring the dynamic input tool to split out the transactional data on the way back in. Do you have any additional guidance on this tool?

Charlee

Nick, this has been really helpful! Thank you!

asifk

Hi Ryan,

This is a fantastic tool, Thanks!

I was able to read in multiple files/ sheets with different schemas and now able to split it according to the sheet name.

This has solved my first problem, my next is because I have imported different schemas there are fields/ columns with blank data, how do you suggest I remove these and only include the fields relevant to that schema.

Bear in Mind there will be a vast amount of Fields, I'm trying to avoid using a Select tool and manually selecting fields to keep.

See attached below:

blimps.yxdb

saiharish

Awesome. Thanks for this. When I tried doing this for an excel output file, it creates separate sheets instead of different files. Is there anything I could do to change that?

Thank you again. Very helpful.

Quick Links

This months top contributors

AkimasaKajitani 387

mceleavey 383

mbarone 337

Hollingsworth 335

LanisC 335