Group similar columns by name to clean data

[Anmol_Mehrotra]

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?

[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 · Accepted solution]

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

[flying008]

Hi, @Anmol_Mehrotra 

The below flow like @Qiu :

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