Data manipulation

[peterg97]

Hello all, 

I am hoping someone will be able to help me with my data manipulation problem. I have a quarterly sales file that I am trying to use Alteryx to transform and automate. I will attach an example of the data that I receive below. I will be receiving this every quarter with the latest figures in them. Please see below for what I receive:

I would like to transform the data so that it is in the same format as the other data I use, so that it can be combined easily. Please see below for an example of how I want the data to look: 

If anyone knows any techniques to do this via Alteryx I would really appreciate it. 

Thanks!

[joshuaburkhow · Accepted solution]

Hey @peterg97 

This isn't the whole thing but wanted to get you something to start. You'll need to use the reporting tools to do the formatting into excel (which is totally possible). Hopefully this helps!

[peterg97]

Hi @joshuaburkow, 

Thanks for this! Solved my issue perfectly, really appreciate it!

Peter

[cgoodman3]

So when I have nested headers the trick I like to employ is as follows:

1) When bringing in the file configure it so the first row contains data, this will force the field names to be F1, F2, F3 etc

2) Isolate the header rows using a select records tool

3) Transpose the data to get a name and value column where Name is the field names F1, F2, F3... and the values are the header rows (in the example the year and the quarter)

4) Use a summarize tool group by name and concatenate the values, and use a delimiter (a pipe '|' is usually useful as it's unlikely to be in your data) so that you can split it later on using.a text to columns tool

5) Isolate your main dataset by selecting records after the header rows

6) Use a dynamic rename tool to rename the F1, F2, F3 etc to the concatenated name created in step 4

7) Transpose your data again, this will then bring the newly created field names down, which you can use a text to column to split out into the new column names (in this case year and quarter)

😎 Cross tab the data back up using the person name (I've called it [Person] instead of [Name] as I know the transpose tool will want to create a field called Name and it saves creating one called Name2) and year

If you follow that approach you should get a fairly repeatable process for dealing with nested headers.

Note this approach doesn't create the merged header at the top with sales as shown in your example, but like @joshuaburkhow suggests this will need the reporting tools to output formatting to Excel.