topic Re: Best way to approach cleaning up a file with multiple similar columns in Alteryx Designer Desktop Discussions

Best way to approach cleaning up a file with multiple similar columns

irmb — Thu, 13 Jun 2024 21:55:14 GMT

HI newbie here. Hello, team members. I am working on a file that should be easy to clean up but I am trying to find the most efficient way to do so. The file has multiple columns but this are the critical ones (in this format):

Employee id	last name	first name	Course A title- status	Course A title- completion date	Course B title - stat	Course B title- completion date	Course C title- st	Course C- completion date
1234	blue	ivy	completed	12/8/19	In progress
1234	blue	ivy			completed	12/8/19
1234	blue	ivy					In progress

I want it cleaned like this:

Employee id

last name

first name

Course title

status

completion date

The biggest issue are these:

a) there are about 10-15 columns with the completion status and an additional 10-15 columns with the title of the course and the completion date. The course title columns are not uniform. For example, sometimes the status column is named "Course title" stat, sometimes "Course title" status", sometimes the column has the course title but no "status" in the column's name. Same with the "completion" heading. I tried using Regex to first cleaning up the titles so they would be uniform and then join every thing using the column name. But that did not work because I could not figure out how to use regex to remove all unwanted characters and have all columns the same name.

b) The other issue is, there are several records for the one person. So, sometimes we end up with having about 30 records for the person to indicate their status for the 10 courses, because each record indicates the completion of the course.

I hope this is clear. I tried using transpose and formula using regex but I am a bit stuck now.

Thanks

Re: Best way to approach cleaning up a file with multiple similar columns

MichalM — Mon, 15 Jul 2019 19:51:21 GMT

@irmb

Would it be safe to assume that

hyphen is used consistently to separate a course title and the rest of the field (status/completion date)?
status is always followed by completion date for the given course?

Re: Best way to approach cleaning up a file with multiple similar columns

MichalM — Mon, 15 Jul 2019 20:28:36 GMT

If the above is the case, this is how I'd go around approaching the problem - workflow attached

Re: Best way to approach cleaning up a file with multiple similar columns

dho111 — Mon, 15 Jul 2019 21:03:22 GMT

I created Record ID for each row and did not remove empty rows.

Re: Best way to approach cleaning up a file with multiple similar columns

irmb — Tue, 16 Jul 2019 01:56:23 GMT

Thanks, I think the last approach may work. It is similar to what I started. Let me try it out.

Re: Best way to approach cleaning up a file with multiple similar columns

estherb47 — Tue, 16 Jul 2019 02:44:28 GMT

Hi @irmb

OK, so with the similar columns, I wanted to create a workflow that can easily adjust based on number of Courses. So transpose is definitely the way to go. You can also leverage the dynamic tools to help you. I separated the course status from the completion dates to easily summarize, and remove where there are too many rows per course respondent.

In the dynamic rename tool, I used a Regex Replace to take only the "Course X" string from the header. Assuming that the pattern is always course title status column, followed by the completion date, this will rename the status column as Column X, and the completion date column as Column X2.

Dynamic Select separates the fields representing status from completion date, so that we can transpose and summarize, and return only one row for each course for each respondent (if someone was in progress and then completed, the formula tool changes the value to Completed. I threw in the Data Cleansing tool to standardize case in the status, and clean up extra whitespace.

Join everything back together, rename your columns, and hopefully this all works for you.

Let me know if this helps. I tried to make it as dynamic as possible.

Cheers!

Esther

Re: Best way to approach cleaning up a file with multiple similar columns

irmb — Tue, 16 Jul 2019 04:19:59 GMT

HI guys, thanks for your great ideas. I was testing some of the ideas out and realized something. The column names were not standardized "course name - status" "course name - completion date" because when the Excel is imported into Alteryx, some of the column names are truncated. How do I adjust the input tool so that it does not truncate the column names? I searched on the group and saw that I need to change the field length. Looks pretty straightforward but I don't get that option when I see the input tool's options. Screenshot below.

Re: Best way to approach cleaning up a file with multiple similar columns

MichalM — Tue, 16 Jul 2019 09:20:12 GMT

@irmb

I don't think this is possible with Excel files. Also, the field length applies to the actual values rather than Header names. Could you try moving the headers into the first row of data by selecting the option below? This will hopefully prevent them from being truncated after which you can process them and move them back into the original position by Dynamic Rename

Re: Best way to approach cleaning up a file with multiple similar columns

estherb47 — Tue, 16 Jul 2019 12:18:01 GMT

Hi @irmb

If the pattern of headers is status followed by date, the method I proposed will work even if the column names are truncated. It’s only looking for the word course and the corresponding letter.

Please do try it and see if it solves your challenge.

Cheers!
Esther

Re: Best way to approach cleaning up a file with multiple similar columns

irmb — Tue, 16 Jul 2019 19:37:30 GMT

@MichalM I'm testing out your solution and it seems to work pretty well except using the - delimiter. Unfortunately, some of the course names use the - several times and not only to separate the status or completion names from the course name. Is there a way to set the delimiter to be - followed by the initial letters of the words status and completion?

Re: Best way to approach cleaning up a file with multiple similar columns

MichalM — Tue, 16 Jul 2019 19:38:56 GMT

You'd need to use RegEx Parse to do that. If you could send over couple of scenarios I can give you an idea about how to do this.

Re: Best way to approach cleaning up a file with multiple similar columns

irmb — Tue, 16 Jul 2019 19:57:15 GMT

Here is a sample

Quiz: Species - Humans - Gender -Nationality - Status

Quiz: Species - Humans - Gender -Nationality - Completion

Assessment: Customer - Review - Networks - Status

Assessment: Customer - Review - Networks TeleNetworks - Status

Re: Best way to approach cleaning up a file with multiple similar columns

MichalM — Wed, 17 Jul 2019 14:36:49 GMT

Do you want to check the attached?

(.+)(- st[a-zA-Z]+\>|- com[a-zA-Z]+\>)

Re: Best way to approach cleaning up a file with multiple similar columns

irmb — Tue, 23 Jul 2019 22:19:54 GMT

@MichalM Awesome thanks! I tried the formula and overall it worked great, except in the cases where the word status ended with a 2 like so "status2" in those cases, the regex out ended up with "null".

Also, is there a place where I can learn how to form expressions in Alteryx? I have no idea what you sent means. I want to be able to create those on my own.

Thanks for your help,

Re: Best way to approach cleaning up a file with multiple similar columns

MichalM — Wed, 24 Jul 2019 07:58:16 GMT

@irmb

This is because the expression only expects - st or - com followed by any number of letters. If you wanted this to match both status and status2 as well as an eventuality where a number is added to completion, it would look like the below

(- st\w+\>|- com\w+\>)

The expressions we use here are called Regular Expressions and are not Alteryx specific. It is a language of sort that helps you express patterns in a piece of text. Having said that, there are number of great resources available on Alteryx Community that will help you learn more about them

Re: Best way to approach cleaning up a file with multiple similar columns

irmb — Wed, 24 Jul 2019 15:52:07 GMT

YOu are awesome! I combined both expressions and it worked! Thanks