Hi
I am looking to clean data coming from an API before it could be added to the final output. Currently the raw data is following:
Header | Data | Count |
First Name | Adam | 1 |
Last Name | Joe | 1 |
URL | example.com | 1 |
FirstName1 | Joe | 2 |
LastName1 | Smith | 2 |
URL | abc.com | 2 |
FirstName | John | 3 |
LastName | Doe | 3 |
URL | abc.us | 3 |
Doing a cross tab to create a table is getting me the following:
Count | First Name | FirstName1 | FirstName | Last Name | LastName1 | LastName | URL |
2 | Joe | Smith | abc.com | ||||
1 | Adam | Joe | example.com | ||||
3 | John | Doe | abc.us |
However, I want to clean the data and have it appear something like the following so it can be further joined properly:
Count | First Name | Last Name | URL |
2 | Joe | Smith | abc.com |
1 | Adam | Joe | example.com |
3 | John | Doe | abc.us |
How can I achieve this? Can anyone please help me out?
Solved! Go to Solution.
To achieve the desired result of cleaning and restructuring the data in Alteryx, you can follow these steps:
Transpose Tool:
Filter Tool:
Formula Tool:
Cross Tab Tool:
Select Tool:
@Anmol_Mehrotra
I hope your header is not having too many variants. 😁
@Qiu Thanks for such a simple solution. Worked like a charm. I had underscore as well so just tweaked the formula tool to include that as well and it worked just fine.
@Anmol_Mehrotra
Glad it helps.