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
¡Resuelto! Ir a solución.
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