Weekly Challenge

Solve the challenge, share your solution and summit the ranks of our Community!
New content is available in Academy! You may need to clear your browser cache for an optimal viewing experience

Challenge #35: Data Cleansing Practice

Highlighted
Magnetar
Magnetar

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

Highlighted
Asteroid

👍

Highlighted

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

 

 

 

Highlighted
Alteryx Partner

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
Alteryx Partner

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
Highlighted
Alteryx Certified Partner

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

Highlighted
Meteor

Here is a solution

Highlighted
Alteryx Certified Partner

Solution attached (again)

 

Spoiler
challenge_35 - Snip.PNG
Highlighted
Meteor

A little longer but got there. 

Highlighted
Alteryx Certified Partner

Here is my solution.