Alteryx Designer Knowledge Base

Definitive answers from Designer experts.

How to combine multiple header rows into one

Alteryx
Alteryx
Created

 

Sometimes, data will come with multiple header lines, for example, year and quarter might be broken into two rows, like this: 

 

9-15-2016 3-00-57 PM.png

 

That is not very helpful when you are trying to analyze your data. 

 

You could use a Select Tool and type in new names OR you could do a few transformations and use the Dynamic Rename Tool to merge the rows dynamically, so you are prepared when 2017 rolls around the corner and they start adding new columns to your file! 

 

To fix data that looks like the above, we start with the Sample Tool to split the data into two data streams - one with just the values and one with the header information. Then, we transpose the data to let us fill in the missing years and use the Summarize Tool to concatenate the resulting field values: 

 

9-15-2016 2-58-32 PM.png

  

Now we can use the header lookup we created as a right input into a Dynamic Rename Tool using the "Take Field Names from Right Input of Rows" option and selecting "Name" as the old field name and Concat Value as the new combined field name. 

 

See the attached workflow for details of tool configurations. 

Attachments
Comments
12 - Quasar

Very helpful! Thank you for writing this article!

11 - Bolide

And this works even if you have blanks at the top of the spreadsheet and headers on three different rows, you just have to add another Sample tool which was my problem. Thanks for sharing! 

9 - Comet

Great tutorial @HenrietteH, thank you. I was about 80% towards making this work before I gave in and looked for help!

7 - Meteor

Thanks @HenrietteH, this was really useful!

5 - Atom

thanks a lot @HenrietteH . it really helped.