Community Spring Cleaning week is here! Join your fellow Maveryx in digging through your old posts and marking comments on them as solved. Learn more here!

Alteryx Designer Desktop Discussions

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

Columnar Data Consolidation

d_van_g
5 - Atom

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 ID123412341234432143214321
Entity NameEntity IEntity IEntity IEntity IIEntity IIEntity II
Interest Income       2,345  534  
Dividend Income        3,424  645 
Capital Gains         4,455     7,876.0

 

Desired Outcome

Entity ID12344321
Entity NameEntity IEntity II
Interest Income       2,345534
Dividend Income       3,424645
Capital Gains       4,455       7,876
3 REPLIES 3
Joe_Mako
12 - Quasar

min name.png

- 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

d_van_g
5 - Atom

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!

Joe_Mako
12 - Quasar

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!

Labels