Urgent Data cleansing

Hi there, I had an issue trying to cleanse a data for comparison. The data have multiple columns that contain concatenates, e.g. some rows in column C show “ Argentina|2010|March|Economy” and some rows in column D show “Spain|2023|June|growth”.. I did multiple text to column bit then it created a lot of blank fields, and sometimes the columns don’t match their respect headers (e.g need to delete some of the blank cells). Just wondering if there’s any quicker way of doing this cleansing in Alteryx?

Tips and Tricks

Parse

Help

Preparation

Accepted answers

NJT

Would need a bit more information to know if this would help you out, but if you're already using Text to Column I'm guessing there is more to the story here. Can you put up some sample data? For me using the example you describe here two text to column tools set to parse "|" in column C and then in Column D seems to do the trick if you're talking about the same row of data.

Column C Column D

If it's two rows of data then it gets more complicated and I think you can handle it with a multi-field formula tool or Regex, but I'm still trying to figure that one out.

Two text to column tools gives me

My guess is you're trying to get column C and D to parse into the same columns? (i.e. Country, Year, Month, Measure)

estherb47

Hi @Anniefang!

Assuming the first row is actually data, and not headings, here's my solution. Easiest way to deal with things being in different columns is to use the transpose tool to get them all into one column. A filter tool can get rid of the extra rows, including nulls, that result.

Cheers!!

Esther

DataCleanse.yxmd

Sushantmutreja1

Yes you can, just need to increase the "Number of Columns" from 3 to the number you want your desired result (you just need to click on Text to Column tool and on the left side change the number 3 to what is required).

All comments

NJT

Column C Column D

If it's two rows of data then it gets more complicated and I think you can handle it with a multi-field formula tool or Regex, but I'm still trying to figure that one out.

Two text to column tools gives me

My guess is you're trying to get column C and D to parse into the same columns? (i.e. Country, Year, Month, Measure)

Anniefang

Hey!

Thanks for the reply!

The data set was quite big, and I did three times of text to column as the concatenates are showing in both column B, C, D. However, as you have done, it ended up with many null cells. I was only looking for the year, month, country and the long number, so was wondering if I could extract these using Regex but am not sure how to use the expressions.