Do you use Alteryx in a language other than English? If so, we want to hear from you! Please help us improve the international experience of our products by participating in this 5 minute survey.

We are updating the requirements for Community registration. As of 7/21/21 all users will be required to register a phone number with their My Alteryx accounts. If you have already registered, you will be prompted on your next login to add your phone number.

Alteryx Designer Discussions

Find answers, ask questions, and share expertise about Alteryx Designer.
SOLVED

Transform data resulting in empty cells

dwillequer
6 - Meteoroid

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:

CategoriesValues
Category 1Value 1
Category 2Value 2
Category 2Value 3
Category 3Value 4
Category 3Value 5
Category 3Value 6
Category 4Value 7
Category 4Value 8
Category 4Value 9
Category 4Value 10

 

End Result that I am currently reaching:

Category 1Category 2Category 3Category 4
Value 1   
 Value 2  
 Value 3  
  Value 4 
  Value 5 
  Value 6 
   Value 7
   Value 8
   Value 9
   Value 10

 

Desired Result:

Category 1Category 2Category 3Category 4
Value 1Value 2Value 4Value 7
 Value 3Value 5Value 8
  Value 6Value 9
   Value 10
3 REPLIES 3
jeff_reynolds
10 - Fireball

Try the attached workflow. It does what you're looking for. 

 

 

dwillequer
6 - Meteoroid

Thanks for the quick response! I guess I will have to do some reading up on how to use the Multi-Row Formula tool.

MichaelLaRose
10 - Fireball

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

Labels