topic Challenge #35: Data Cleansing Practice in Weekly Challenges

Challenge #35: Data Cleansing Practice

GeneR — Tue, 13 Sep 2022 16:58:17 GMT

The link to the solution for last challenge #34 is HERE. For this challenge let’s practice some data cleansing.

Use Case: There is a series of data cleansing processes we need to do on our data. Please solve each per the instructions.

Objective:

Remove leading zeroes
Trim leading zeroes and/or descriptive text at the end
If the data value ends with ID, remove the ID
If more than 8 chars, remove anything after 8. If only 6, add “SC” to the front.

Re: Weekly Exercise #35: Data Cleansing Practice (Beginner)

MattD — Mon, 08 Aug 2016 15:09:44 GMT

Here's a solution:

Re: Challenge #35: Data Cleansing Practice

mceleavey — Tue, 26 Feb 2019 05:18:10 GMT

My solutions.

1. Remove Leading Zeros.

For this, given the codes we are looking for are 4 digit numeric codes, I simply converted to a numeric field. This automatically drops the leading zeros:

2. Isolate 4 digit numeric code.

Here, I simply used Text to Columns to split the columns on ":". I then dropped the superfluous columns:

3. Remove "SD" from the end of any records on which it appears.

This was achieved by simply using the "Trimright()" function to remove any instances of "SD":

4. Return 8 character string and add "US" to the start of strings that are shorter than 8 characters.

I used a simple IF statement to determine the records that were shorter than 8 characters, and if so, add the characters "US" to the beginning. This was coupled with a left() function to return the 8 character string:

5. Return 5 digit code rather than descriptions on given codes.

Here, I used the join tool to join "SBU Code" in the input data stream to the "Description" field in the source table. The left output returns the correct three codes. The output of the join shows those records where the code is in the description field. Rename this output and union back to the input stream to return all records correctly:

Re: Challenge #35: Data Cleansing Practice

ankit_mandal — Tue, 26 Feb 2019 05:28:02 GMT

Here's my solution for this challenge.

Re: Challenge #35: Data Cleansing Practice

LandonG — Wed, 13 Dec 2017 13:40:57 GMT

Re: Challenge #35: Data Cleansing Practice

Mattpannuzzo — Fri, 16 Mar 2018 23:39:16 GMT

Hi Syfer,

You're close.

I realigned your columns in the union join which returned the correct result.

- Matt

Re: Challenge #35: Data Cleansing Practice

Samuel_To — Wed, 27 Feb 2019 01:07:32 GMT

Hi @GeneR ,

Here is my solution.

Best Regards,

Samuel

Re: Challenge #35: Data Cleansing Practice

TonyA — Wed, 27 Feb 2019 01:33:50 GMT

Not sure what's going on with this challenge. None of the replies I'm seeing line up with the current version of the question. Here's my solution.

Re: Challenge #35: Data Cleansing Practice

estherb47 — Wed, 27 Feb 2019 02:40:29 GMT

Redone.

Re: Challenge #35: Data Cleansing Practice

Alekh — Wed, 27 Feb 2019 09:29:36 GMT

This challenge was so difficult that barely anyone in the last few years have been able to solve it! :P

Re: Challenge #35: Data Cleansing Practice

Aidan_K — Wed, 27 Feb 2019 09:38:43 GMT

Here's my version 2

thanks,

Re: Challenge #35: Data Cleansing Practice

RolandSchubert — Wed, 27 Feb 2019 11:02:16 GMT

My solution.

Re: Challenge #35: Data Cleansing Practice

danilang — Wed, 27 Feb 2019 11:33:10 GMT

Once more unto the breach, dear friends, once more!

Dan

Re: Challenge #35: Data Cleansing Practice

pasccout — Wed, 27 Feb 2019 12:20:56 GMT

Here is my solution to the 2nd iteration of this challenge...

Re: Challenge #35: Data Cleansing Practice

cplewis90 — Wed, 27 Feb 2019 13:54:13 GMT

Re: Challenge #35: Data Cleansing Practice

patrick_digan — Wed, 27 Feb 2019 18:31:01 GMT

Re: Challenge #35: Data Cleansing Practice

kat — Wed, 27 Feb 2019 19:55:46 GMT

Here is my take 2!

Re: Challenge #35: Data Cleansing Practice

OldDogNewTricks — Wed, 27 Feb 2019 23:10:18 GMT

My solution to the updated start file.

Re: Challenge #35: Data Cleansing Practice

jasperlch — Thu, 28 Feb 2019 03:17:44 GMT

Solution attached.

Re: Challenge #35: Data Cleansing Practice

Neetu — Thu, 28 Feb 2019 07:19:39 GMT

Solution v2