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

Transposing row to columns - with multiple columns belonging to 1 new column

Deron__
5 - Atom

Dear community,

 

I have the following situation and I have not found any solution to this yet.

 

I currently have the following table:

 

Deron___3-1590646819546.png

 

And after the Alteryx workflow I would like to have the following table:

 

Deron___4-1590646858475.png

 

Does anyone know how to create the workflow for this? Thank you very much!!

 

8 REPLIES 8
PhilipMannering
16 - Nebula
16 - Nebula

Hi @Deron__ 

 

Does this work?

 

PhilipMannering_0-1590648614250.png

 

grazitti_sapna
17 - Castor

Hi @Deron__ ,

 

Please find attached the screenshot and Workflow for your reference. I hope it helps.

 

 

Sapna Gupta
Deron__
5 - Atom

Thank you for your prompt reply!

 

Sorry, I believe I had to write the table differently. The product columns are not necessarily unique (so not necessarily type 1, type 2 and type 3 etc in the columns). Rather this is how I could have my dataset (see below). As you can see there is no unique ID for each column, the columns could be exacty the same (so every record is Type 1, every record has discount rate of 1.0% and every record as years of 10), which is not a problem for the purpose of my analysis but all I want is that the product, discount and years old are the column names and everything else falls under that. See below what I mean: 

 

Deron___0-1590651458567.png

 

And it should become:

 

Deron___1-1590651560739.png

 

Thank you so much!

PhilipMannering
16 - Nebula
16 - Nebula

Could you provide the input as a spreadsheet or text input? I think that the column tables should be unique. Alteryx data will enforce this - columns need to have unique names. But if you provide the input in any format you like, I can have a look.

Deron__
5 - Atom

Hi Philip,

 

Please see attached a test file in xlsx. In the input sheet you will see how the data is and in the output sheet how I would like it to see in Alteryx. 

 

Thank you very much for looking into this!

 

 

PhilipMannering
16 - Nebula
16 - Nebula

Hey @Deron__ . No problem.

 

So the fields do indeed get renamed when you import the spreadsheet.

 

See attached a very similar solution to before. I've used the Tile Tool and Dynamic Rename to keep the columns headers as they should be (otherwise punctuation and spaces get converted to underscores). Take a look at the attached...I think it works (you may need to reconnect the input file).

Deron__
5 - Atom

Hi Philip,

 

Thank you - it seems to work indeed! Would the below solution be a simpler version of what you created or are there any downsides to this? See attached. 

PhilipMannering
16 - Nebula
16 - Nebula

You're welcome.

 

Yes, your solution is totally valid also. I guess the only downside is that there are some rather unattractive underscores in your column names. You could just rename them with your select tool, but that would be less dynamic (i.e. not update if the field names changed in the input file). The plus side is, as you say, that it is simpler of course!

 

I think you can go ahead and set both of our solutions as accepted 😉😉

Labels