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

Normalizing Data with Multiple Column Headers

jcastles003
6 - Meteoroid

I have a large database that keeps track of income data.  It has multiple headers for each column, and I am having difficulty figuring out how to normalize the data.

 

Below is an example of what input I receive.  The amounts presented represent income earned.  It needs to remain dynamic, because the number of entities (columns) and number of states/revenue types (rows) with amounts changes with each input received.

 

 ParentHoldco 1Holdco 1Holdco 2
 SubsidiarySub 1Sub 2Sub 1
StateRevenue Type   
ALInventory Sale10020050
ALRental Income15030050
MSInventory Sale20040050
MSRental Income25050050

 

Below represents the normalized data output that I would like.

 

ParentSubsidiaryStateRevenue TypeAmount
Holdco 1Sub 1ALInventory Sale100
Holdco 1Sub 1ALRental Income150
Holdco 1Sub 1MSInventory Sale200
Holdco 1Sub 1MSRental Income250
Holdco 1Sub 2ALInventory Sale200
Holdco 1Sub 2ALRental Income300
Holdco 1Sub 2MSInventory Sale400
Holdco 1Sub 2MSRental Income500
Holdco 2Sub 1ALInventory Sale50
Holdco 2Sub 1ALRental Income50
Holdco 2Sub 1MSInventory Sale50
Holdco 2Sub 1MSRental Income50

 

I basically have no idea how to get it in that format and am completely lost.  I've been playing around with CrossTab, Summarize, etc, and cannot figure out a way to convert it.

5 REPLIES 5
RodL
Alteryx Alumni (Retired)

Attached is an example of how you might approach this. I think the key is to separate out the headers and deal with them separately, and then union back in the actual data.

I'm doing this using the Select Records tool with the assumption that you always have the three rows that are your "headers". Another assumption is that the top two header rows are consistently named Parent and Subsidiary. But this should be dynamic from the standpoint of an unlimited number of Parent/Subsidiary combinations.

And finally I'm hard-coding the final column names.

Hope this gets you closer to a solution. Smiley Happy

jcastles003
6 - Meteoroid

This is exactly what I'm looking for.  Thank you so much!

Joe_Mako
12 - Quasar

Attached is a similar approach that uses less tools.

 

For the first 2 records, a Transpose and Cross Tab combination reshapes and renames, for the data in records 4+, a Transpose will make the data tall, a Transpose of record 3 preps it for use in renaming the data, and then it can be joined together with the header data with a Join tool.

 

Multiple Header Lines.png

RodL
Alteryx Alumni (Retired)

Very nice, and much more elegant! Smiley Happy

msmith
7 - Meteor

from_to_Qty.gif

 

Your solution looks like what I need, but I only have one header row.  I need to normalize to 4 columns, From, To, SKU, and qty. I'm new to Alteryx and a little confused on which pieces to cut out.  thanks.

Labels