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

Pivoting a table on its axis

zdavis
7 - 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!

11 REPLIES 11
JohnJPS
15 - Aurora

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

jdunkerley79
ACE Emeritus
ACE Emeritus

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

zdavis
7 - 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!

JohnJPS
15 - Aurora

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
7 - 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!

RodL
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 Smiley Wink).

KaneG
Alteryx Alumni (Retired)

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
5 - Atom

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