We've recently made an accessibility improvement to the community and therefore posts without any content are no longer allowed. Please use the spoiler feature or add a short message in the message body in order to submit your weekly challenge.
The Alteryx Community will be temporarily unavailable for a time due to scheduled maintenance on Thursday, April 22nd. Please plan accordingly.

Weekly Challenge

Solve the challenge, share your solution and summit the ranks of our Community!

Also available in | Français | Português | Español | 日本語
IDEAS WANTED

We're actively looking for ideas on how to improve Weekly Challenges and would love to hear what you think!

Submit Feedback

Challenge #35: Data Cleansing Practice

GeneR
Alteryx Alumni (Retired)

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:

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

Here's a solution:

Spoiler
Exercise 35 Solution.PNG
mceleavey
14 - Magnetar

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
6 - Meteoroid

Here's my solution for this challenge.

Spoiler
000002.png
LandonG
8 - Asteroid
 
Mattpannuzzo
7 - Meteor

Hi Syfer,

 

You're close.

 

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

 

Spoiler
syfer889fix.png

- Matt

Samuel_To
9 - Comet

Hi @GeneR ,

 

Here is my solution. 

 

Best Regards,

Samuel

 

TonyA
Alteryx
Alteryx

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.

EstherB47
15 - Aurora
15 - Aurora

Redone.

Alekh
8 - Asteroid

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