Community Spring Cleaning week is here! Join your fellow Maveryx in digging through your old posts and marking comments on them as solved. Learn more here!

Alteryx Designer Desktop Discussions

Find answers, ask questions, and share expertise about Alteryx Designer Desktop and Intelligence Suite.
SOLVED

Need Guidance with Transpose and Export to Multiple Files

thd-data-analyst
7 - Meteor

Hello Kind Folks,

 

I have a question that is troubling me.

 

I have data that goes like this:

 

CategoryAttributeValues
Cat1Attrib1Ball, Bat, Mat
Cat1Attrib2Man, Women, Boy, Girl
Cat1Attrib3Car, Bike, Bus
Cat2Attrib4Red, Blue, Green
Cat2Attrib5Sofa, Table, Chair, Cabinet

 

I want to export one file for each Category. So the Cat1 file must be in this format:

 

Attrib1Attrib2Attrib3
BallManCar
BatWomenBike
MatBoyBus
 Girl 

 

Cat2 file must be like this

 

Attrib4Attrib5
RedSofa
BlueTable
GreenChair
 Cabinet

 

I know I need to transpose the data and export into multiple files. Ideally I should be doing the transpose for each category and then export and go to the next category and so on. Can anyone suggest me how I can do this?

11 REPLIES 11
BrandonB
Alteryx
Alteryx

Workflow is attached that can reorient your data as shown below. Then, you can use an Output Data tool to "Take File/Table Name From Field" if you want to dynamically create files from the resulting table. 

 

data reorienting.png

thd-data-analyst
7 - Meteor

Thank you very much @BrandonB, I have a follow up question. I have 13K rows in my file so potentially after transpose, there will be 13K columns in the table. Do you think that will be a problem?

BrandonB
Alteryx
Alteryx

If you are okay using a relative attribute number by category rather than having a separate attribute, this may work better as an alternative which wouldn't create so many columns:

 

data reorienting v2.png

thd-data-analyst
7 - Meteor

Unfortunately my Category-Attribute combination is unique. I can't use a relative attribute number.

BrandonB
Alteryx
Alteryx

Gotcha. The other approach would be to turn this workflow into a macro to feed in groups of categories in one at a time which would only output the attribute columns specific to each category rather than all 13,000

thd-data-analyst
7 - Meteor

Understood @BrandonB This is very helpful. I will convert this to a macro and feed it one Category at a time. Thanks for all the help.

BrandonB
Alteryx
Alteryx

macro approach attached!

 

macro approach.png

macro example.png

BrandonB
Alteryx
Alteryx

This will give you outputs that don't have all of the extra columns as shown below

 

excel output.png

thd-data-analyst
7 - Meteor

Thank you @BrandonB I was able to change my workflow to a macro using the approach you showed in the example workflow and macro.

 

Although I see that the output files have the attributes that belong to all the categories in each file.

 

CategoryAttrib1Attrib2Attrib3Attrib4Attrib5
Cat1BallManCar  
Cat1BatWomenBike  
Cat1MatBoyBus  
Cat1 Girl   

 

I want to have the attributes that only belong to the respective category in each category file.

 

Can you please suggest how I can do that?

Labels