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

Transpose rows under 1 field and concatenate with values from different fields

JoRao
8 - Asteroid

Hello All - This is my first post here. I am trying to convert the rows under one field to different  field names and concatenate each field name that was derived from rows with other field names. I have attached a sample worksheet showing what I want my end product to  look like.

 

Thanks in advance for your help!

 

11 REPLIES 11
JordanB
Alteryx
Alteryx

Hi @JoRao

 

Please find a workflow attached which can show you how to manipulate your data.

 

Pic4.png

 

Some of these techniques such as the two tool containers at the end can be replicated once you add product 2, 3 & 4 in the data.

 

Best,

 

Jordan Barker

Solutions Consultant 

JoRao
8 - Asteroid

Hi @JordanB,


Thank you for your response. I guess I was not very clear in describing what it is that I want to achieve. If we looked at row 1-4 on the excel sheet I attached, I want to be able to concatenate the field names (Avg, Product1, etc,.) to the values in the first row (manual PMPM, PCC PMPM, etc.) So my end result would have field names that look like this:

 

 

Avg Man PMPM **, Avg PCC PMPM **, Avg Right PMPM ** ,Avg Final PMPM ** ,Product 1 Man PMPM **  etc.

 

How would I achieve this in Alteryx?

 

Thanks again for your help!

 

Best,

Jo

JordanB
Alteryx
Alteryx

Hi @JoRao

 

My fault for not interpreting correctly. 

 

Again similar techniques will be used but the key is to pivot the data so your field headers and 'Avg Man etc. are side by side. You can then concatenate the field headers and avg etc. values together and then pivot the data to your ideal format. 

 

Attached is a workflow you can use as a basis.

 

Best,

 

Jordan Barker

Solutions Consultant

JoRao
8 - Asteroid

This is awesome!! Thank you so much for your help @JordanB!!

JoRao
8 - Asteroid

Hi @JordanB -

 

How would I convert this into a batch macro which I can then use with multiple excel files?

 

Thanks!

Jo

JordanB
Alteryx
Alteryx

HI @JoRao

 

I have attached the batch macro but you will need to create the file paths to feed into it.

 

This will require a directory tool to get a list of all your xlsx files. You will then need to use a formula tool to add the specific sheet name to each xlsx file.

 

If you go to the solution in this article it shows the directory and formula steps to add the sheet name to the full path field.

 

***Each file will need to be have the same structure e.g. you use a select records tool 1-4, so if one file onl misses the first 1-3 you will have to think of another dynamic logic for this batch macro to work for all files.

 

FYI. Download the macro workflow, save it somewhere and then on a new canvas right click>>>Insert>>Macro. You can then use the directory and formula to build and process your xlsx files. 

 

Best,

 

Jordan Barker

Solutions Consultant

JoRao
8 - Asteroid

Thanks @JordanB. Because I am using xls files instead of xlsx, I am unable to pick the sheet names using the method suggested in the solution on your link. How would I set the sheet name in this scenario?


Thanks again for all your help!

 

Best,

Jo

JoRao
8 - Asteroid

Please ignore my previous message. I was able to pull in the specific sheet name using formula tool. 

 

Thanks!

Jo

JoRao
8 - Asteroid

Thank you again for all your help @JordanB. I am now trying to create a macro where in I input two files and do a join mulitple by record position so I can keep adding columns from the output files. Some sheets could have the same field names as others. For example, if Sheet 1 has field names Product 1, Product 2 etc, another file could also have Product 1 along with other products. How would I account for that so that all the selected fields in each file show up in the output? Here is an image of my workflow:

Multiple Join Workflow.JPG

 

How can I make this into a macro so I can process multiple excel files?

 

Thanks!

Jo 

 

Labels