Hi Alteryx Community,
I have a scenario where my data set contains a series of columns. Each column has data tags for the Entity ID and Entity name (always 1 to 1 match), and a numerical value. The data is organized so that each entity has a column for each "income type". I would like to manipulate the data so that each Entity ID has one column, containing all of the income types.
I have unsuccessfully attempted a few ideas around transposing and find replacing the values. Any assistance is greatly appreciated.
Data Set Example
Entity ID | 1234 | 1234 | 1234 | 4321 | 4321 | 4321 |
Entity Name | Entity I | Entity I | Entity I | Entity II | Entity II | Entity II |
Interest Income | 2,345 | 534 | ||||
Dividend Income | 3,424 | 645 | ||||
Capital Gains | 4,455 | 7,876.0 |
Desired Outcome
Entity ID | 1234 | 4321 |
Entity Name | Entity I | Entity II |
Interest Income | 2,345 | 534 |
Dividend Income | 3,424 | 645 |
Capital Gains | 4,455 | 7,876 |
Solved! Go to Solution.
- Record ID to uniquely identify each record for reshaping
- Transpose to to make the data tall, with Record ID and Field1 as Key
- Multi-Field Formula to convert Name to a number, eg from "Field2" to 2
- Filter to keep Entity values
- Summarize to get the Min_Name per Value
- Join on Name, so we now have a list of Names and the Min_Name
- Join on Name back with full data
- Filter to remove Empty values
- Cross Tab to spin the data back to shape returning the First value
Thanks Joe,
Really appreciate the help on this, your solution works like a charm. Would you mind explaining how Min_Name works in the Summarize tool, and the "theory" behind your approach?
I'm new to Alteryx, and am still getting up to speed.
Much appreciated!
A number of things are a factor here:
1. The connection to the data is set to "Data in first row", so the field names are "Field1", "Field2", etc.
2. The Transpose tool reshapes the data, creating two fields "Name" and "Value".
3. The Multi-Field Formula tool will remove the word Field from the "Name" values and convert to an integer.
4. The Filter tool will just keep the cells in the original record that has all the Entity IDs
5. The Summarize tool will group by the Entity ID, and return the minimum "Name", the lowest Field Number, this way we get one original column Name per Entity ID
Does that help? We can also setup a Webex if you would like to discus in greater detail. Thank you!