Transformation of DATA from wide format table into a vertical format

[grromerro]

Hi Alteryx Community,

I'm reaching out for your help in building a sample workflow that transforms a dynamic wide format table into a normalized long format, while preserving tier based pricing per product.

I've been exploring multiple approaches over the past few days using tools like Dynamic Rename, Transpose, and others, but I haven’t been able to get close to the desired result. I’d really appreciate your insights and support in constructing a working example.

What I need to do:

I receive a file each week in the same structure as File 1 Start, and I need to reshape it to match File 2 Result.

Key challenges:

Product names change weekly:  they can be any type of material, so the solution must dynamically adapt to new column names.

The number of product columns vary; it can increase or decrease depending on the week.

Tier scales also vary depending on the product.

This is the look of the initial file:

This is the desired result:

I am not savvy with Macros so I would love and I was trying to get the results using only tools no macros.

Thank you!!!

  

[Fgill]

If your key columns are remaining the same then this may quickly be solved with a formula and Union tool.  The formula tool to add a product column for each, and the Union to stack each one on top of each other.  

Perhaps an analytic App to tie it all together.  Putting a file browse and action tool on your input so the user can choose the file and sheet.  A text tool and action on the formula so the user can state the product, followed by the union and then output.


hope that helps

[binuacs]

@grromerro One way of doing it with a batch macro

[grromerro]

This solution worked perfectly, thank you! I do have a follow-up question, as I’m not very familiar with using macros. I copied and pasted the macro icon into my workflow and tested it with this week’s data and it worked great. If I save the workflow to a shared drive or upload it to the Alteryx Gallery (not for scheduling, just for others to run as needed), is there anything else I need to do to ensure the macro works correctly for other users?

Thanks again!

[binuacs]

@grromerro 

• Include all macros used in the workflow:

  • If the macro is external, package it along with the workflow.

  • Use Options > Export Workflow to create a .yxzp (packaged file) so dependencies (macros, input files) are bundled.

• Make sure macros are properly linked relatively, not using absolute paths. - this part is important, make sure you saved the macro in a shared location and use that macro in your workflow