I am new to the alteryx discussion boards. I am trying to adjust data. I have seen other question/answers that solve a portion of my issue, but not completely.
I am trying to get data like this:
To look like this:
Thank you for helping
Hi again @ameynder
So first thing that's causing wonkiness in the workflow is that the Excel file isn't consistent with the Merged and Centered fields. If you merge and center cells AN1:AR2, then Alteryx can read them in as being in the same row as Inbound and Outbound. The hidden column in Excel is also causing some challenges (assuming this is because of a drop down list needed for the column before it). Now the workflow looks at columns A-AR only (this is based on the headings you chose to prioritize on Sheet1Modified the workflow just a touch. Instead of assuming that the first row contains headers, set the options to the first row contains data. That makes the columns easier to deal with, as far as matching up the rows correctly.Also, deselected the "Dynamic Fields" options in all of the tools that had that option. I noticed that the performance of the workflow was off - fields kept changing as I ran the workflow more than once. So, you might have to fix the columns again if this isn't your real data. I'm unsure why, but Alteryx would drop some columns along the way, which could also be why it wasn't quite there.
Give this a run and see if it grabs what you need.Cheers!
Esther
Hi @ameynder I mocked up a workflow that I think answers your question. I have commented my workflow as much as possible to explain what I have done. Are you able to provide any sample excel in order to test?
Hi @ameynder
Here's a solution that uses mostly the Favorites tools.
Basic logic here is to separate out the multiple column headers, and then join the data back together. The first row grabs the Area headings, transposes them to rows, and fills in blanks with a Multi Row formula tool. Then I added an Area Order field to help with sorting later.Next grab the Function headings (the second Select records tool). Transpose again, and add a field for ordering.Last Select Records grabs the rest of the data. The transpose tool on the rows above has a simple F3-F11 schema on the Names column. We'll need the same to match up the rows back together. So, a little adjusting first. Add the column name to the value in columns F3-F11 with a Multi Field formula tool. Then use Dynamic rename to pop the column headers into the column headers row. Transpose to make the data vertical, as with the two top streams in the workflow. Next we need to pull out the F3 - F11 for the Join Multiple, so we can use Regex_Replace. Finally a simple Replace formula fixes the Name and Value columns to remove the F3-F11 headers.The one piece that needs adjustments with your data will be the Text Input tool. There, you can type in the individual Measures and the order by which they should be sorted.A Join Multiple tool rebuilds the rows, by matching up the F3, F4, F5, etc.Then a sort to reorder the rows, and a Select to reorder the columns.
Let me know if that helps!
Cheers,
@EstherB47,
I was able to get very close with your workflow, but I can't get all the way there. I have fuller data set. Let me know your thoughts
This seems to be optimized and gets the end format correct, which is awesome. I looked at the totals though and they do not match. The original sheet shows 1.7M for the count of "# of ctns" where the alteryx output says 5203.
I checked one more time and it works ties out! Thank you so very much for your help. I appreciate you helping me here!
