Transpose tool - Transform

[Ravy12]

Hello Experts,

How can I transform below table to desired output.

input:

movie Name	10 Cloverfield Lane	13th	20th Century Women	1945	1985
Number of Critical Reviews	301	95	217	67	43
Rating	90%	97%	89%	97%	95%
Year	2016	2016	2017	2017	2018

Output:

Movie Name	Number of Critical Reviews	Rating	Year
10 Cloverfield Lane	301	90%	2016
13th	95	97%	2016
20th Century Women	217	89%	2017
1945	67	97%	2017
1985	43	95%	2018

[grossal · Accepted solution]

Hi @Ravy12,

here is a quick way to solve your problem.

I know other people will probably use different tools, but for me, that's the quickest solution.

The Python-Tool only needs one line to fully shift the data and the dynamic rename is used to use the first data row as column headers.

Example workflow attached to see all configurations.

If this solved you problem, please consider to accept it as a solution or drop another question if it doesn't.

Alex

[danilang · Accepted solution]

Hi @grossal 

Python definitely takes the win in terms of number of tools.  Here's how you can do it without calling in the snake

 

If you don't care about row order in your output, you're pretty well done after the Dynamic Rename.  To get the correct row order, use a Field Info to get the original column names and add a RowOrder.  Join this to output, sort and clean up

Dan  

[grossal · Accepted solution]

Hi @danilang,

it's kinda sad that there isn't a "Shift full data" tool by default tbh.

I have tried to motivate myself to not use Python when using Alteryx, but I can't withstand when I come around this problem. I was running into the exact same issue multiple times and even create a macro for it to make it a "one tool" thing. 

I hope we get a tool or option in a tool to access this problem in the future.

[Ravy12]

Hi  @grossal 

Can you please advise how to configure python tool in Alteryx designer.

I don't have Python tool in my designer.

Regards, Ravi

[grossal]

Hi @Ravy12,

what version of Alteryx Designer are you using?

I am not sure when they added the Python Tool, but it should be part of 2019.4 and 2020.1 by default.

[Ravy12]

Hi @grossal 

I am using version 2018.1.4.44311

Regards, Ravi

[grossal]

Hi @Ravy12,

they released the Python Tool with version 2018.3. If you are able to upgrade, I would actually recommend going to 2019.4 (2020.1 isn't fully stable right now).

If you aren't able to upgrade, please use the solution provided by @danilang.

Regards,

Alex