In case you missed the announcement: Alteryx One is here, and so is the Spring Release! Learn more about these new and exciting releases here!

Alteryx Designer Desktop Discussions

Find answers, ask questions, and share expertise about Alteryx Designer Desktop and Intelligence Suite.
SOLVED

Group similar columns by name to clean data

Anmol_Mehrotra
6 - Meteoroid

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:

 

HeaderDataCount
First NameAdam1
Last NameJoe1
URLexample.com1
FirstName1Joe2
LastName1Smith2
URLabc.com2
FirstNameJohn3
LastNameDoe3
URLabc.us3

 

Doing a cross tab to create a table is getting me the following:

 

CountFirst NameFirstName1FirstNameLast NameLastName1LastNameURL
2 Joe  Smith abc.com
1Adam  Joe  example.com
3  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 NameURL
2JoeSmithabc.com
1AdamJoeexample.com
3JohnDoeabc.us

 

How can I achieve this? Can anyone please help me out?

6 REPLIES 6
Hammad_Rashid
11 - Bolide

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
21 - Polaris
21 - Polaris

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

1121-Anmol_Mehrotra-A.PNG1121-Anmol_Mehrotra-B.PNG

flying008
15 - Aurora

Hi, @Anmol_Mehrotra 

 

The below flow like @Qiu :

 

录制_2023_11_21_14_48_46_738.gif

Anmol_Mehrotra
6 - Meteoroid

@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
21 - Polaris
21 - Polaris

@Anmol_Mehrotra 
Glad it helps.

Manoj_k
9 - Comet

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

Labels
Top Solution Authors