Community Spring Cleaning week is here! Join your fellow Maveryx in digging through your old posts and marking comments on them as solved. Learn more here!

Weekly Challenges

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.

SUBMIT YOUR IDEA

Challenge #35: Data Cleansing Practice

rahofnouse
7 - Meteor

Solution

Gapo
7 - Meteor

Here is my solution to the challenge! Useful one.

TheOC
15 - Aurora
15 - Aurora

That was fun!

Spoiler
Good practise for using the formula tool and the different built in functions of it, and also a little outside-the-box thinking for removing text/trailing zeros.

TheOC_0-1597835339794.png

Converting the string to a number meant that it removed leading zeroes, an elegant and easy solution!




TheOC_3-1597835426481.png
Additionally, converting a string to a number also removes any text/symbols. Still works as an elegant solution but does produce an error within the console. I imagine there's a solution for me to find here that would get around this...




TheOC_4-1597835463548.png

Removing text from data in a certain circumstance? Formula tool made sense to me to use. I originally was looking at implementing an IF statement to check the end of the text for the "ID" text, but i had found that TrimRight took care of the whole situation for me, how useful!




TheOC_5-1597835570706.png
Okay so this one required a bit more of a formula, but when you break it down its quite simple:
TheOC_0-1597836981762.png

I start with what I consider the easiest step, if the length of the string is 6, then the text should be "SC" and then the original string.
Then, if this isn't the case, check if the length of the string is over 8 digits. If this is the case then create a new string - using the sub-string function - that creates a string from the 0-8 characters of the original string (first 8 characters).
If neither of these are the case, then just return the original String.
And end the If statement either way.




Bulien
WernerE
8 - Asteroid

Completed.

TheOC
15 - Aurora
15 - Aurora

A really nice challenge! Lots of useful skills and thinking outside the box a little!

Spoiler
TheOC_0-1597866429199.png

I removed the leading zeroes by converting the string to an integer - this did the job for me, and then simply change it back to a string.


TheOC_1-1597866474884.png

Interestingly, converting it to an integer on the second challenge also produced the final result - it removed all symbols and letters. It does produce an error in the results window, so I would be drawn to say this isn't the best method for it, but it works!




TheOC_2-1597866545304.png

I originally started this using an if statement formula. What was interesting for this was reading through documentation on Alteryx for the formula's, and seeing what was possible. It turns out there's a function for this specifically - TrimRight!




TheOC_3-1597866615125.png

I definitely couldn't find a way to not use an IF-ELSE statement on this one however, and does the job perfectly. It does look a little complex at first but broken down its really simple:

TheOC_0-1597867056476.png


With cases like this, I find it easiest to start with the simplest case of situation - if the code is 6 characters. Using length, i can compare the length of the data, and then in the case it is I create a new string output of "SC" plus the original text.

Then i check for the other condition, if the data is more than 8 letters, then I only want to take the first 8 letters. I used the Substring function for this, as it allowed me to pick where I want my new string to start (0) and end (8).

In the case that it doesn't fit either of these conditions, then I just want it to return the original data, and end after any condition has been hit!







Bulien
scbrownsr
6 - Meteoroid

Hi,

 

I mainly solved using the formula tool with the Trim, Right, and IIF functions.

 

I'm not too proud of the last one, but since this was my first challenge, I figured I'd have some fun. 🙂

 

Thanks,

 

Shawn

 

2020-08-19_16-01-16.jpg

Qiu
20 - Arcturus
20 - Arcturus

Where is #5?

Spoiler
en-35.PNG
MaheshaAS
6 - Meteoroid

.

Owaisf
7 - Meteor

OwaiSSolution

 

 

I just used  "Formula" tool

Spoiler
Owaisf_0-1598230590578.png

 

luke_chowaniec
6 - Meteoroid

My attempt