Restructuring/ Grouping : cross tab & transpose

Hello All,

I would highly appreciate any help or guidance on this one.

I am struggling to re-arrange my raw data from the tabular column format to horizontal format. I am new to Alteryx and kind of stuck at this stage.

Raw Data is in the first sheet and desired format in the second one.

Thank you!

Trail.xlsx

Output

Accepted answers

danilang

Hi @Madooxno9

Building on @JoshuaGostick's excellent start, here's a workflow that splits the column names into rows

The bottom branch is what I added. It uses the Field Info tool to get the metadata and then splits the column names into rows before crosstabbing and renaming to get the original column names back. These are unioned with the original data to give you

Dan

Restructuring Grouping cross tab and transpose v2.yxzp

JoshuaGostick

Hi @Madooxno9,

The workflow I've attached should convert your data exactly like it looks like in the excel file. The workflow might be slightly more complicated than necessary because I tried to keep it as dynamic as possible. Let me know if this works for you or if you have any issues.

Thanks,

Josh

Restructuring Grouping cross tab and transpose v2.yxmd

All comments

JoshuaGostick

Hi @Madooxno9,

I've built a workflow that should set you on the right way. What I did was combine the values to be used as the new field titles into one value, and then used these new values to crosstab.

Transpose and Cross Tab.PNG

The output isn't formatted exactly like it is in your excel spreadsheet because you normally work in a tabular format with Alteryx. However, let me know if you need help in formatting it exactly like it is in the excel file.

Thanks,

Josh

Restructuring Grouping cross tab and transpose.yxmd

afv2688

Hello @Madooxno9 ,

Hope this helps

Regards

revenues-volume.yxmd

Madooxno9

Hi JoshuaGostick,

This is somewhat closer to my initial approach and thanks for the solution. I still need to figure out how to set it up to my final desired format, as my database will not be able to read this format. What would you recommend I should do to break down " Local_Volume_2019_June_Adj " into separate rows?

Thank you once again and for your time.

Quick Links

Unanswered
Community Events
Groups

This months top contributors

mceleavey 383

mbarone 337

Hollingsworth 335

LanisC 335

JeffF 335