Urgent Data cleansing

[Anniefang]

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?

[estherb47]

Sure! You can adjust the filter so that the Regex_CountMatches function looks for more than zero matches (REGEX_CountMatches([Value], "\|")>0)

The shorter fields won't be parsed by the RegEx parse tool. So add a formula tool afterwards and fix the first column from the RegEx parse tool with the following function:

Cheers!!

[NJT]

Thanks for sharing the problem and the additional data and letting us know a solution worked for you, this will definitely come in handy for future community members when looking for a solution to similar problems.