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. challenge_35_start_file.yxmd challenge_35_solution.yxmd

Done challenge_35_solutionAG.yxmd

My Solution Spoiler 35 Solution.yxzp

Challenge #35: Data Cleansing Practice

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.

challenge_35_start_file.yxmd

challenge_35_solution.yxmd

Parse

Preparation

Data Analysis

Core

Basic

Accepted answers

All comments

MattD

Here's a solution:

Spoiler

mceleavey

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:

Remove leading zeros.PNG

2. Isolate 4 digit numeric code.

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

Isolate 4 Digit Code.PNG

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":

Trim SD from the end of a string.PNG

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:

Trim to 8 Characters and add US.PNG

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:

Isolate 5 digit codes.PNG

ankit_mandal

Here's my solution for this challenge.

Spoiler

LandonG

Mattpannuzzo

Hi Syfer,

You're close.

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

Spoiler

- Matt

Samuel_To

Hi @GeneR ,

Here is my solution.

Best Regards,

Samuel

challenge_35_work_file.yxmd

TonyA

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.

challenge_35a_TonyA.yxmd

estherb47

Redone.

challenge_35_EHB_completed.yxmd

Alekh

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

35..yxmd

Aidan_K

Here's my version 2

thanks,

challenge_35_New_AidanKing.yxmd

RolandSchubert

My solution.

challenge_35_updated_version_solution_rsc.yxmd

danilang

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

Dan

challenge_35_Solution_DL_2.yxmd

pasccout

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

challenge_35_my_solution - v2.yxmd

cplewis90

Spoiler

challenge_35_CL.yxmd

patrick_digan

Spoiler

challenge_35_start_filev2.yxmd

kat

Here is my take 2!

Spoiler

Challenge #35 take 2.yxmd

OldDogNewTricks

My solution to the updated start file.

challenge_35_completed.yxmd

jasperlch

Solution attached.

challenge_35_JL.yxmd

Neetu

Solution v2

Solution_challenge_35_V2.yxmd

JoshuaGostick

Thought this looked familiar!

Spoiler

challenge_35_JG_v2.yxmd

ggruccio

Solved a second time!

challenge_35_gg_finish_version2.yxmd

JosephSerpis

Challenge Completed

challenge_35_start_file.yxmd

catarina_pimpao

Redone.

AWC_35.yxmd

ahmadtookhy

Challenge 35 Solution

challenge_35_Toukhy_Solution.yxmd

RichoBsJ

Hi! Here my new solution

challenge_35_richobsj_second_time_solved.yxmd

ipeng

my solution

IP_challenge_35_start_file.yxmd

IgnasP

I saw a 5th assignment on several first solutions, but it isn't in the starting file.

What am I missing?

challenge_35_start_solution_mine.yxmd

AmNotARobot

Learned a few new tricks.

challenge_35_solution AmNotARobot.yxmd

Thableaus

Challenge #35, done.

That was easy. I'm not even going to spoiler tag it, it's just a few functions.

Cheers,

Challenge_35.yxmd

NicoleJohnson

My solution!

Spoiler

Used a single Formula tool to solve each one:

1. TrimLeft([Field1],"0")
2. REGEX_Replace(TrimLeft([Field1],"0"),"(\d+).*","$1")
3. If Right([ID],2)="ID" Then Replace([ID],"ID","") Else [ID] Endif
4. If Length([Safety Code])=6 Then "SC"+[Safety Code] Else Left([Safety Code],8) Endif