Merge Data based on Unique Column Headers

[bwyeth]

Hi All, 

I am looking to merge values under unique column header names. In the dataset some column names are repeated so there is alot of redundancy and white space in the data. Please see attached dummy input and output files which hopefully describe what I am after.

Is there a way (or tool i can use) that can do this dynamically in Designer? i.e. without having to define the column header names in a formula?

Thank you very much for any help you can give.

Ben

[Tyro_abc · Accepted solution]

Hi Ben

Check if the attached workflow is working as per your expectation, let me know if you need explanation of any step

[echuong1]

This should achieve what you're looking for.

I used a transpose to pivot the data so all of the columns were not rows. Alteryx doesn't allow more than one column with the same name, so I used a data cleansing to remove the numbers that were appended to the field names. This is represented by the Name column after the transpose. Once I had the data cleaned, I used a cross-tab to pivot the data back. There is a group by on Unique ID to get every ID on a separate row, the name field became the headers, and the value the values.

Hope this helps!

[bwyeth]

Thank you for your reply, much appriciated