Alteryx designer Discussions

Find answers, ask questions, and share expertise about Alteryx Designer.
Alteryx is here to help you solve your biggest data challenges. Read about the new Virtual Solution Center here.

Splitting output based on data

Highlighted
6 - Meteoroid

Hello, 

 

I have an input file that looks like this.

Twinkle95M_1-1581354454057.png

 

I want to get the output something like this. The output is based on Master Number. E.g If Master number if 101 then everything which has 101 in master number, they all will be in one file, for which we have 105 then everything will be in a separate file and so on. For each name, we are creating a column. 

First file: 

Twinkle95M_3-1581354570253.png

second file: 

Twinkle95M_4-1581354614532.png

Highlighted
10 - Fireball

You can use the Cross Tab tool to convert the E_number records into columns, then you can group your output by the Master number to split the output...

Highlighted
Alteryx Certified Partner
Alteryx Certified Partner

So dynamically writing these records to different files is the easy part. I used the following IF statement in my example:

 

"%temp%\"+ToString([Master Number])+".xlsx|Sheet1"

 

You can then configure an Output tool to use this new field to write each record to the appropriate file.The difficult part is writing these files with different fields. For this reason, I recommend using a batch macro to write each file. 

 

Check out the attached workflow+macro that shows how this can be done. 

Highlighted
Alteryx Partner

@Twinkle95M - This is a great use case for a Batch Macro!  In the attached solution, the summarize tool will create a list of the unique Master Numbers in the data set, and the Batch Macro will filter for each independently, create the cross tab table structure, update the file path a write out an individual output file with only those data points and fields.  Upon completion of the first iteration, the macro will proceed to the next item, and so on, until the list is complete. 

 

2020-02-10 overview.PNG

 

Batch Macro canvas:

2020-02-10 macro.PNG

Alteryx
Alteryx

hi @Twinkle95M ,

 

you can use a combination of the prep and transform tools to achieve the end output. hope this helps!

 

bpatel_0-1581357184131.png

Highlighted
14 - Magnetar
14 - Magnetar

Hi @Twinkle95M 

 

Give this a go and let me know if it works, please.

 

At first, seemed like a simple solution that the Output tool could handle, because of its ability to separate the data into different sheets/tables (Excel, Access) or different files (any file type) based on a field.

 

But, the columns in your example need to be dynamic too.

 

A batch macro can feed each unique Master number into the main macro, which filters the data based on that master number. A transpose/formula/summarize finds which columns are empty, and a filter eliminates those from the result. Then, join back to the original data, transpose, and output within the macro. I have the macro outputting to my temp directory.

This isn't an exact match to your output, so you'll have to tweak it a touch.

 

Cheers!

Esther

Highlighted
8 - Asteroid

I saw Name and E_number have relation 1 - 1. So I create a batch macro to do. You can see attached file

Labels