Happy 8th birthday to the Maveryx Community! Take a walk down memory lane in our birthday blog, and don't miss out on the awesome birthday present that all Maveryx Community members get to take advantage of!
Bold Text Example

The localized versions of the Core Certification will be discontinued on September 22, 2023. To take the exam in your preferred language, please schedule it before this date. The Core exam will still be available in English at any time after September 22, 2023. If future versions of the Core Certification exam are localized, we will promptly announce their release dates.

alteryx Community

# 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

Want to get involved? We're always looking for ideas and content for Weekly Challenges.

## Challenge #35: Data Cleansing Practice

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:

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.
Alteryx Alumni (Retired)

Here's a solution:

Spoiler
Former Alteryx, Inc. Support Engineer, Community Data Architect, Data Scientist then Data Engineer
17 - Castor

My solutions.

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:

6 - Meteoroid

Here's my solution for this challenge.

Spoiler
8 - Asteroid

7 - Meteor

Hi Syfer,

You're close.

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

Spoiler

- Matt

10 - Fireball

Hi @GeneR ,

Here is my solution.

Best Regards,

Samuel

Alteryx Alumni (Retired)

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.

15 - Aurora

Redone.

9 - Comet

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