Transpose and Append

[AIG_Dave]

Hi all,

I'm relatively new to Alteryx and am looking for a way to transform my data in a specific way. The best way I can explain is that I want to transpose some row data into column data and list all possible permutations of this row data--similar to what the append tool does.

I've attached Excel files with an example of how my data looks now ("Before" file) and how I want it to look ("After").

Thank you for any help.

[AIG_Dave]

P.S. If there's an elegant way to do this without filtering each individual Factor and using a bunch of Append tools, I would appreciate that.

[estherb47 · Accepted solution]

Hi @AIG_Dave 

This might not be the most elegant solution, but it does work. The logic is to clean up the data (remove null rows, copy down headings), and then crosstab horizontally.

Starting with the right-most field that needs parsing into rows, use Text to Columns split to rows. That way, a new row is generated for each instance. You keep moving backwards through the columns until all rows are parsed. This adds all of the rows you need.

Let me know if this helps.

Cheers!

Esther

[AIG_Dave]

Thanks for the quick response, Esther.

This solution does help with moving the Factors/Groupings into their own columns, but it also omits the % Multiplier column. Those multipliers need to be included.

[AIG_Dave]

Oops! I realized that my "After" file was not correct. Corrected version is attached.