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

[ryan_richardson]

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!

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

[ryan_richardson]

Hi- Could you provide some more context on your desired output? Is your goal to stack the data so that the columns align? If so, a Union tool would be perfect.

If not, it seems like if some columns apply to certain rows, there will inherently be a mismatch-

I can work through this I think with a bit more information-

Thank you

[asifk]

Hi Ryan,

Thanks for your reply, I managed to find a solution to this already. 

after reading upon pages and pages in the community I was able to find a Macro that does what I required. see attached

[asifk]

My apologies,

The Goal was to btain only the columns relevant to that specific row:

Sheet Name	Name	Amt
ABC	Justin	20

Sheet Name	ID	Status
XYZ	58869	A

[ryan_richardson]

That's great news- I got as far as arranging the sample data as shown below- will review the macro and see if I was on the right track-

Sheet Name Name Value
ABC              Name Justin
ABC              Amt     20
XYZ              ID        58869
XYZ             Status    A
DEF            Game    Soccer
DEF             Point     58

Thanks-

[asifk]

Hi


I think it might be due to to when the macro is run some field names are renamed eg Amount will be _Amount_

If you have more than 5 to split , an alternate option will be to edit the macro and just add more filter and output tools , remember to edit each filter condition and also macro output number

Hope this helps

Sent from my iPhone

[jay_chang]

Nick, I'm using your macro but I'm running into a bizarre problem.  I have more than 5 items to split, so I've connected the 5th output of your macro to the input of another of your macros.  While both macros seem to run correctly, when I place a select tool try to rename the output coming out of the 2nd macro, Alteryx is not accepting it.  The workflow runs fine but the Select tool's renames are not being carried through into the outputs from the 2nd of your macros.  Any idea why that might be happening?

Actually now that I examine the flow more closely, none of the renames are being reflected in any of the Browse tools.  Everything else works perfectly but the renames in the various Select tools are not being reflected.  Help, please?

UPDATE - Please ignore.  This is completely due to operator error.  Thank you for providing such a useful tool.

[Bozhena]

Hi Ryan,

Thank You for your macro. I was trying to use it in my workflow. It works but I'm getting an error since number of splits in my case exceeds 5. I know that I can split the rest of the report by adding one more macro but that doesn't get rid of the error in the first macro. I wanted to update your macro and add 3 more splits but after I copy pasted and update # within your macro it doesn't seem to work.

Thank You!

Bozhena

[asifk]

Hi, I have also added more filters as my data required multiple splits.

when doing so, you need to update the Macro output value such that all are unique in increments of 1.

eg output 1

output 2

and so on

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