community
cancel
Showing results for 
Search instead for 
Did you mean: 

Alteryx designer Discussions

Find answers, ask questions, and share expertise about Alteryx Designer.
Upgrade Alteryx Designer in 10 Steps

Debating whether or not to upgrade to the latest version of Alteryx Designer?

LEARN MORE
SOLVED

Pivoting a table on its axis

Highlighted
Meteor

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!

ACE Emeritus
ACE Emeritus

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)

 

Meteor

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

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.

 

transpose.jpg

 

Can't post a workflow so posted to DropBox

Meteor

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!

ACE Emeritus
ACE Emeritus

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."

 

Meteor

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!

Alteryx Alumni (Retired)

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 :smileywink:).

Alteryx
Alteryx

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.

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.

Labels