on 09-16-2016 01:27 PM - edited on 07-27-2021 11:36 PM by APIUserOpsDM
Sometimes, data will come with multiple header lines, for example, year and quarter might be broken into two rows, like this:
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, westart with the Sample Tool to split thedata 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:
Now we can use the header lookup we created as a right input into a DynamicRename 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.
Very helpful! Thank you for writing this article!
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!
Great tutorial @HenrietteH, thank you. I was about 80% towards making this work before I gave in and looked for help!
Thanks @HenrietteH, this was really useful!
thanks a lot @HenrietteH . it really helped.
great solution! thanks for sharing