Group similar columns by name to clean data
- Subscribe to RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Mute
- Printer Friendly Page
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
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.
- Labels:
- API
- Connectors
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
To achieve the desired result of cleaning and restructuring the data in Alteryx, you can follow these steps:
Transpose Tool:
- Select the columns you want to transpose (e.g., "Header," "Data," "Count").
- Configure the tool to transpose the "Header" column and pivot the "Data" and "Count" columns.
Filter Tool:
- Remove rows where the "Data" column is null or empty.
Formula Tool:
- Create new fields for "First Name," "Last Name," and "URL" using conditional statements based on the values in the "Header" column.
Cross Tab Tool:
- Configure the tool to use the "Count" column as the record ID, the newly created fields as the column headers, and the "Data" column as the data values.
Select Tool:
- Choose only the columns you want in the final output (e.g., "Count," "First Name," "Last Name," "URL").
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
@Anmol_Mehrotra
I hope your header is not having too many variants. 😁
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
@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.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
@Anmol_Mehrotra
Glad it helps.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
