Pivoting a table on its axis

[zdavis]

In Excel, you can select a table of data and "Paste Transpose". This feature then pastes the data, but pivots the paste on the X and Y axis. Neither "Transpose" nor "Crosstab" tools are capable of this function presently (trust me, I've tried every combination possible with either tool or both in conjuction), and it is sometimes necessary to perform a simple pivot of the data on the axis.

I have had to work around this to manipulate my data into a pivoted form on several occassions, and it is quite a painstaking process. Other times, I have simply output my data into excel, performed the function, and re-uploaded the pivoted dataset downstream. It would be fantastic to have a simple tool that would perform this basic function, and nothing else.

If Excel can do it, Alteryx should be able to do it!!

Thank you!

[JohnJPS · Accepted solution]

I agree this would be useful, and easy for them to implement.  For the time being, you can do the same with the R tool:

df <- read.Alteryx("#1", mode="data.frame")
write.Alteryx(as.data.frame(t(df)), 1)

[zdavis]

Hmm... that's very cool. I am not a R coder, though I know how to copy/paste into an R tool. I tried that, and it errored out on me: Error: unexpected symbol in "df <- read.Alteryx("#1", mode="data.frame") write.Alteryx".

Any thoughts on what I'm doing wrong? I'd love to make a macro for myself using this code if I can get it ironed out. That'd be awesome.

Thank you!!

Best Regards,

Zak Davis

[jdunkerley79 · Accepted solution]

That is a fun a challenge.

Like the R solution.

It is do-able with transpose and crosstabs combined with recordid and multiple row formula but far from straight forward.

Can't post a workflow so posted to DropBox

[zdavis]

Wow... Thank you JohnJPS and jdunkerley79 for both posting great solutions. Still trying to get the R solution to run, but hopefully someone can chime in and tell me what I'm doing wrong. 

The crosstab / transpose solution is great as well, and I'll definately keep this in my back pocket for the next time I run into this issue.

Thank you both!

[JohnJPS]

Hi zdavis, please make sure there's a line feed prior to "write.Alteryx......" (as seen in my two lines of code above).  I think perhaps the line feed was lost during the "paste."

[zdavis]

JohnJPS,

That was the issue (sorry, total rookie at R here). Thanks for pointing that out to me. That's a great solution for integer-based data. Looks like I have two great solutions in my back pocket for this now!

Thank you very much!

[RodL]

And if you want to have it as just one 'tool' in Alteryx, I think you can use the attached. (Note...NOT extensively tested, but easily tweaked ).

[KaneG]

Exactly what I was thinking @RodL, I just had RecordID > Transpose > Crosstab but I prefer yours putting the F in front of the column names.

[GreggLathrop]

very new to alteryx, I got this macro to work with 1 file.(2 columns, 58 rows)> converted as expected to 2 rows with 58 columns.

When I tried to run this on *.csv (35 identical structured files) it created an output of 2 rows and 2030 columns. How do i get it produce 35 rows(+header) and 58 columns

I basically need to take my tall data in each file and "paste special transpose" then combine into 1 master file that is wide

I too tried "cross tab" tool, but I couldn't figure it out.