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

NicoleJohnson
ACE Emeritus
ACE Emeritus

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

Cheers!

NJ

IJReed
8 - Asteroid

👍

kelly_gilbert
13 - Pulsar

Version 2 with the new data sets:

 

 

Spoiler

Part 1:
regex_replace([Field1],'^0*','')

Part 2:
regex_replace(
  regex_replace([Field1],'^0*',''),
  '[^\d]',
  ''
)

Part 3:
regex_replace(
  [ID], 'ID$', ''
)

Part 4:
if Length([Safety Code]) <= 6 then
  'SC' + [Safety Code]
else
  left([Safety Code], 8)
endif

 

 

 

OllieClarke
15 - Aurora
15 - Aurora

Here's my solution round 2:

Spoiler
4 formula tools:

1 - TrimLeft([Field1],'0')

2 - REGEX_REPLACE(TrimLeft([Field1],'0'),'\D','')

3 - REGEX_Replace([ID],'ID$','')

4 - IF LENGTH(Left([Safety Code],8))=6 THEN 'SC'+LEFT([Safety Code],8)
ELSE LEFT([Safety Code],8)
ENDIF
vizwiz
5 - Atom

Good practice. I only needed one formula tool for each question.

Spoiler
Screenshot 2019-03-13 at 1.41.11 pm.pngScreenshot 2019-03-13 at 1.41.04 pm.pngScreenshot 2019-03-13 at 1.40.59 pm.pngScreenshot 2019-03-13 at 1.40.52 pm.png
Verakso
11 - Bolide

For all of the new ones that wonder how come there isn't more solutions, or why some of the old solutions seems differently, then it is because all submitted solutions that contained data, was removed from this challenge due to technical reasons.

 

But all though I have previously made this one before, it actually was a fresh re-take of this "old" challenge.

 

So instead of "just" resubmitting my old solution, I made this one again, and I can see, that I have learned a lot just in that brief time since I last did it - thanks to the team behind the weekly challenges

 

Spoiler
So this time I only used the Regular Expressions to solve the challenge
The first two was "easy" since they could be done using the RegEx tool
The second two was a bit more tricky, especially the last one, since the Regex_match formular does not have the exact same behavior as in the RegEx tool.
My 2. go at thisMy 2. go at this

 

Still Climbing

/Thomas

mambo
7 - Meteor

Here is a solution

PhilipMannering
16 - Nebula
16 - Nebula

Solution attached (again)

 

Spoiler
challenge_35 - Snip.PNG
AdamBiggs
7 - Meteor

A little longer but got there. 

Suzu
9 - Comet

Here is my solution.