SIMPLE TRANSPOSE, HOW IS IT DONE?

[andrebrandaobritob]

I'm having a lot of trouble trying to do a simple transpose like we do in excel.

My worksheet is like the image 1.jpg, I need to do a simple transpose so it shows like the image 2.jpg.

Can you guys help me accomplish that? I attached the sample excel file. 

Thank you!

[alexnajm]

Transpose to move your headers to a column, and THEN Crosstab to move your [Parcela] column to headers!

[TUSHAR050392]

@andrebrandaobritob Another approach which involves multiple use of crosstab tool since it can let you move only 1 column at a time. Use the cross tab tool, select Column A in change column headers, then column 1 as Values for new columns. This needs to be done five time and Column A will remain constant always with Column2,3,4,5. Once this is done, you can union the data from all 5 crosstab tools to get the output.

Hope this helps

[jbichachi003]

@TUSHAR050392 will work, but @alexnajm's solution is the least manual (especially if the final set has a lot of columns). As @alexnajm said, you'll need to do a Transpose followed by a Cross Tab. What was left unmentioned is you'll need another Transpose after the Cross Tab.

A couple of things to note:

1. The default configurations for the two Transposes and the Cross Tab can be left as they are.
2. Once you finish the transformations, you can use a Text to Columns tool to split out the number of columns you'll need (this is manual and if the final data set has more columns you'll need to update this tool).
3. Keep in mind--because your data contains null values, the combination of the two Transposes, Cross Tab, and Text to Columns will yield incorrect results (you'll find the some values shifted over to incorrect columns). To correct this, use a Multi-Field Formula before the first Transpose to replace all Empty values with some unique combination of letters/numbers/characters. Once all the transformations are complete, you'll undo this with another Multi-Field formula.

Best of luck!

[alexnajm · Accepted solution]

Hey @jbichachi003 - can you clarify the need for the Transpose after? I tested this quickly and I don't think it's necessary

More can be done to get the column order/names right, but this is the main drift (didn't want to have to build it but here we are haha)

[jbichachi003]

Hi @alexnajm, I'm glad I posted because I enjoyed seeing your solution to it without the need for the Text to Columns tool. Here was my solution, which is similar to yours, but with the unfortunate use of the Text to Columns tool and the need to use the Multi-Field tool. Unlike your solution, I don't configure the Transpose or the Cross Tab tools.

That said, your solution is more elegant, especially if order is not relevant.

[CoG]

Here is my approach to solving this problem: https://youtu.be/lTrCEbDccb4 (same premise from another recent question)

Also, here is the Custom Macro Tool I built for this purpose: Flip Tool - Alteryx Community

The macro should handle all of the functionality you are looking for and takes into account row and column order + column naming!

Hope this helps and Happy Solving!

[Qiu]

@CoG 
Nice to see you have a YouTube Channel, but I was wondering how did you change your Alteryx Community User name😁

[CoG]

Thanks, @Qiu! I figured it would be a good way to provide more detail to help others learn.

Username Change: Mission Control (alteryx.com)

Then Edit "My Profile" from Preference Center (Took me longer than I'd like to admit to figure that out)

[Qiu]

@CoG 
Thanks for sharing how can we change the user name.