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: HeaderDataCountFirst NameAdam1Last NameJoe1URLexample.com1FirstName1Joe2LastName1Smith2URLabc.com2FirstNameJohn3LastNameDoe3URLabc.us3 Doing a cross tab to create a table is getting me the following: CountFirst NameFirstName1FirstNameLast NameLastName1LastNameURL2 Joe Smith abc.com1Adam Joe example.com3 John Doeabc.us However, I want to clean the data and have it appear something like the following so it can be further joined properly: CountFirst NameLast NameURL2JoeSmithabc.com1AdamJoeexample.com3JohnDoeabc.us How can I achieve this? Can anyone please help me out?

Group similar columns by name to clean data

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?

Connectors

API

Accepted answers

Qiu

@Anmol_Mehrotra
I hope your header is not having too many variants. 😁

1121-Anmol_Mehrotra.yxmd

All comments

Hammad_Rashid

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").

Qiu

@Anmol_Mehrotra
I hope your header is not having too many variants. 😁

1121-Anmol_Mehrotra.yxmd

flying008

Hi, @Anmol_Mehrotra

The below flow like @Qiu :

录制_2023_11_21_14_48_46_738.gif

Anmol_Mehrotra

@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.

Qiu

@Anmol_Mehrotra
Glad it helps.

Manoj_k

Hi, i also prepared something similar, do check it out.

Grouping Similar columns.yxmd

Quick Links

This months top contributors

atcodedog05 19598

Qiu 15879

binu_acs 15708

MarqueeCrew 13708

apathetichell 13703