Export multiple csv based on formula results

[max_hfr]

Hi, so I have a database which contains events (eg. EF11RO9)

Each event can occur multiple times. 

I need to make one csv output per event. So the idea is that i need a formula that could join the same events in a different file. 

 I have more than 2M lines and about 500 000 events so it's pretty impossible to make 500 000 outputs.

So if you have any idea for:

- The simplest formula to achieve the sorting

- The fastest way to export every table as a csv

Thanks in advance 

(Unfortunately i cannot share the dataset)

[DavidP · Accepted solution]

Since I don't know what your data looks like, I've had to mock up a scenario. Hopefully you can adapt it to your situation.

I created a dummy dataset like this:

The 1st thing I did is to group the data by event with transpose and crosstab tools:

Since you now have one row per event, you can build a batch macro that converts each row to the original format and writes it to a csv file. It takes [event] as the control parameter and the whole dataset as the macro input. Within the macro it then filters on one event at a time, like this: (Macro and workflow attached). You'll have to change the output path in the macro.

[max_hfr]

Hi David,  First of All thanks for your answer. When i try to open your file i get another. I believe 1 file is missing. It's my first time using a macro on alteryx so maybe i'm doing something wrong here

[max_hfr]

Hey, i figured out how to see the macro and change the output. You saved my life man :). I hope i'll be able to reproduce the workflow with the real dataset. There is a chance that i will come back to you with another question xD.

Thanks again!

[max_hfr]

Hi David,

I have been struggling to reproduce the macro with another data set from scratch. 
When i run the workflow it only creates a file with the filtered "ID" and doesn't repeat the action with all the "ID". 

Would you be so kind as to try to find the error? I have tried to reproduce it multiple time with no success. I have attached 

my workflow + macro

Thanks in advance,

Max

[DavidP · Accepted solution]

In the macro configuration in your workflow, change the Control GroupBy field to None.

[max_hfr]

It's working thanks to you i've finally started diving into macros :)

[DavidP · Accepted solution]

I just looked through your macro. Two more things you need to update.

1. The Unique value in the Tile Tool should be [Name] rather than [Value]

2. In the Append Fields Tool at the end of the macro, you only want [ID] from the source and Columns 1,2 and 3 from the target. Uncheck  Unknown.

This will work fine for a fixed number of columns (i.e. 3), but if you have variable number of comments the Append Fields Tool in the macro will have to be replaced with a formula tool to add the filename field to the output.  I can have a play around with this and send you a new version.

[max_hfr]

Hey thanks again at the moment i'm only using a fixed number of columns but i may need it for the future. If you have time to show me how it would work it would be nice, but otherwise you've already helped me more than enough!

[DavidP]

Actually, when I looked into how to remove the restriction of 3 columns, I realised that I overcomplicated the whole thing! 

The CrossTab tool in the workflow and the Transpose tool in the macro cancel each other out, so by removing both and making a few minor adjustments, the restriction for fixed columns falls away automatically and I don't need to use the Append Field Tool in the macro if I keep the [ID] field in the dataset throughout.

So here you go, new version attached that can handle any number of columns.