Hi Everyone,
I have data that is structured in an incredibly unfortunate format. Here is a screenshot of this data as is (Excel file attached with input & desired output tabs):
As you can see it's really not pretty. My desired output is below:
However, I'm really struggling to achieve my desired output in Alteryx. I need to automate the generation of the desired output on a monthly basis and unfortunately the values in the "Name" field will change each time (e.g. Claire Gute may not be in next months data pull and instead a new person). Also, some month data pulls may contain more than 3 people and some may have less than 3 people, so the data transformations need to be very dynamic.
Some of my initial thoughts to achieve the desired output would be to dynamically duplicate any of the field names that are a person, then use a Select Records field to remove the top 2 rows, then change the field names to use the value in the top row as the field name. Then ultimately pivot the data to create the Name column.
Unfortunately, you can see in the input that each person has a Summary and Pull Date value in their column before their Order Date information starts.
Is there a way any of this is even possible in Alteryx? Any help to put me in the right direction would be greatly appreciated as I am so stumped right now!
Attached is the excel file and Alteryx workflow.
Thank you,
Paul
