Hello Kind Folks, I have a question that is troubling me. I have data that goes like this: CategoryAttributeValuesCat1Attrib1Ball, Bat, MatCat1Attrib2Man, Women, Boy, GirlCat1Attrib3Car, Bike, BusCat2Attrib4Red, Blue, GreenCat2Attrib5Sofa, Table, Chair, Cabinet I want to export one file for each Category. So the Cat1 file must be in this format: Attrib1Attrib2Attrib3BallManCarBatWomenBikeMatBoyBus Girl Cat2 file must be like this Attrib4Attrib5RedSofaBlueTableGreenChair 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?

Need Guidance with Transpose and Export to Multiple Files

Hello Kind Folks,

I have a question that is troubling me.

I have data that goes like this:

Category	Attribute	Values
Cat1	Attrib1	Ball, Bat, Mat
Cat1	Attrib2	Man, Women, Boy, Girl
Cat1	Attrib3	Car, Bike, Bus
Cat2	Attrib4	Red, Blue, Green
Cat2	Attrib5	Sofa, Table, Chair, Cabinet

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

Attrib1	Attrib2	Attrib3
Ball	Man	Car
Bat	Women	Bike
Mat	Boy	Bus
	Girl

Cat2 file must be like this

Attrib4	Attrib5
Red	Sofa
Blue	Table
Green	Chair
	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?

Transformation

Accepted answers

BrandonB

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

data reorienting.yxmd

BrandonB

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

BrandonB

You are really close! You just need to make sure that the batch macro is set to "Group by" the Category field as shown in this link: https://community.alteryx.com/t5/Alteryx-Designer-Knowledge-Base/Getting-Started-with-Batch-Macros/ta-p/488165

If you download my example above you can see where I have done this

batch macro.png

All comments

BrandonB

data reorienting.png

data reorienting.yxmd

thd-data-analyst

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?

Quick Links

This months top contributors

atcodedog05 19458

Qiu 15867

binu_acs 15708

MarqueeCrew 13708

apathetichell 13703