Alteryx Folks,
I have an interesting data set that I need to manipulate. The file comes from a vendor, and has hundreds of columns that contain 0/1 depending on whether the condition described in the column is true. I need to replace the 1 values with the name of the header, and then ultimately concatenate the values to be more descriptive. Any ideas?
Name | Hot Dogs | Hamburgers | Fries | Shakes |
Bill | 0 | 1 | 1 | 1 |
Toby | 1 | 0 | 1 | 0 |
Sarah | 0 | 0 | 1 | 1 |
In my example, I'd like the resultant table to read more like this:
Name | Hot Dogs | Hamburgers | Fries | Shakes |
Bill | Hamburgers | Fries | Shakes | |
Toby | Hot Dogs | Fries | ||
Sarah | Fries | Shakes |
From there, I'm good with the process to concatenate, but to give you a true visual of my desired end product, it would look like this:
Name | Food |
Bill | Hamburgers; Fries; Shakes |
Toby | Hot Dogs; Fries |
Sarah | Fries; Shakes |
My challenge is moving from the first to second table. I thought I could use a dynamic rename, but based on reviewing the help, I don't think it applies. I don't want to create hundreds of formulas for each column, because they may change from deliverable to deliverable.
Any help is appreciated.
Toby
Solved! Go to Solution.
1. Transpose Data Grouping on Name.
1.a) Name2 should be the food and Value should be 1 or 0
2. Formula Field on field Value.
2.a) IF [Value]=1 THEN [Name2] ELSE NULL() ENDIF
3. Filter tool - !Null([Value]) - take true stream.
4. Summarise Tool. Group By Name and Concatenate Name2. Rename Concatenated Name2 Field to Food.
5. Done.
Thanks to both of you for the prompt response!
Toby