This site uses different types of cookies, including analytics and functional cookies (its own and from other sites). To change your cookie settings or find out more, click here. If you continue browsing our website, you accept these cookies.
on 09-16-201601:27 PM - edited on 04-22-201908:48 AM by SydneyF
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, 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:
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.