Want to get involved? We're always looking for ideas and content for Weekly Challenges.
SUBMIT YOUR IDEAThat was fun!
Converting the string to a number meant that it removed leading zeroes, an elegant and easy solution!
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!
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.
A really nice challenge! Lots of useful skills and thinking outside the box a little!
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.
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!
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!
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:
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!
Where is #5?
.