Pivot question

Hi everyone, I'm new to using Alteryx. I'm working on a process that sources 4 input files with the same format. I've got the data pivoted from multi columns into rows but ultimately I need to have the data in more of a spreadsheet layout.

Any help would be great appreciated.

I have attached a data sample of the the data that is coming out of my transpose tool and filtering out NULL "Value" entries.

And here is the desired format and output that I'm chasing after:

sample_data.csv

Datasets

Accepted answers

ptb55615

Thanks for the great replies !!! I apologize for the issues with the sample data....I couldn't provide "actual" data due to security reasons, so I had to mock up some sample data. I was really excited about the example solutions with the tile tool, but that didn't actually work out for me in my case because I had combined the 4 input files which in total was about 1700 rows with 6 columns and when I transposed the data into rows .... the row count was over 10K. So I couldn't do the "equal records" method of the tile tool. I continued to look around in the Alteryx community and found a previous post that ended up providing a solution for me.

https://community.alteryx.com/t5/Alteryx-Designer-Discussions/Convert-Row-to-Column/td-p/74981

@MarqueeCrew Provided a example solution that worked out great for me. Thanks @MarqueeCrew !!!!

Here's the portion in my workflow that I got working for my situation.

Thanks for everyone's help and I'm amazed of how much great Alteryx help is available via online.

Thanks,

Dwayne

All comments

Luke_C

Hi @ptb55615

Here's two options. In the first I tweaked your data a bit because it seems inconsistent and I'd like to confirm that. The 2nd assumes the data is correct:

Option 1:

Parse out the number from each set of fields as the record ID, remove it from the field names. Crosstab data and re-order
Assumption, inconsistency of data is in error:
- FIELD_ID_1 vs FIELD_1, two sets of data with the ID of 1.

Option 2:

Assign tiles (6 groups of 6) - can also use a multi-row for this if the data set is bigger
Crosstab and rename

New Workflow6.yxmd

AngelosPachis

Hi @ptb55615 ,

Looking at your input, each group of records has 6 columns. What you have to do is find a way to flag those columns and assign them a number from 1-6.