I have a data set that is consistently formatted like the data below and I need to get it formatted as the desired result. The values and categories in the source data set are variable strings. The number of values per category can vary greatly. The goal is to set each category as a column header with the corresponding values as rows underneath that column. I am able to accomplish that currently via Alteryx, but I end up having a lot of unnecessary white space in the table that I am trying to eliminate.
I have tried various combinations of using the Cross Tab tool with the Text to Columns tool to reach my current end result with no success and I feel like I may be overlooking a simple solution to this simple problem. I'd appreciate any insight anyone could lend as I am still new to Alteryx and in the process of going through the academy materials.
Source Data:
Categories | Values |
Category 1 | Value 1 |
Category 2 | Value 2 |
Category 2 | Value 3 |
Category 3 | Value 4 |
Category 3 | Value 5 |
Category 3 | Value 6 |
Category 4 | Value 7 |
Category 4 | Value 8 |
Category 4 | Value 9 |
Category 4 | Value 10 |
End Result that I am currently reaching:
Category 1 | Category 2 | Category 3 | Category 4 |
Value 1 | |||
Value 2 | |||
Value 3 | |||
Value 4 | |||
Value 5 | |||
Value 6 | |||
Value 7 | |||
Value 8 | |||
Value 9 | |||
Value 10 |
Desired Result:
Category 1 | Category 2 | Category 3 | Category 4 |
Value 1 | Value 2 | Value 4 | Value 7 |
Value 3 | Value 5 | Value 8 | |
Value 6 | Value 9 | ||
Value 10 |
Solved! Go to Solution.
Thanks for the quick response! I guess I will have to do some reading up on how to use the Multi-Row Formula tool.
Hi dwillequer,
You can use the join tool to join by position to accomplish your goal.
As long as you do not have a dynamic number of categories, you can split the workflow by each of your categories using a filter tool, and then join them by position.
See attached.
Best,
Michael