Columnar Data Consolidation

d_van_g

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

Joe_Mako

- 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

min name.yxmd