Export multiple csv based on formula results

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)

Datasets

Transformation

Accepted answers

DavidP

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:

event to csv input data.png

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

event to csv grouped.png

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.

event to csv macro.png

event to csv.yxzp

DavidP

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

control groupby.png

DavidP

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.

append fields tool.png

DavidP

I think something went wrong with the filter tool and action tool in your macro.

You can literally just use the macro from my last post in your workflow (as attached to this post)

One key thing to note: In the last version of the workflow and macro that I posted, I moved the Transpose tool out of the macro and into the workflow. That means that the input data structure of the macro has changed (it's a transposed version of the input file grouped to ID), so the macro input has to be updated to this format.

Because you're choosing the input file as a macro input, this is the wrong format, which is why the rest of the macro doesn't work.

If you have any problems importing and running the attached, give me a shout.

Name.yxzp

All comments

DavidP

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:

event to csv input data.png

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

event to csv grouped.png

event to csv macro.png

event to csv.yxzp

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
event to csv error.PNG

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

Workflow csv.yxzp

Quick Links

This months top contributors

atcodedog05 19458

Qiu 15867

binu_acs 15708

MarqueeCrew 13708

apathetichell 13703