community
cancel
Showing results for 
Search instead for 
Did you mean: 
Do you have the skills to make it to the top? Subscribe to our weekly challenges. Try your best to solve the problem, share your solution, and see how others tackled the same problem. We share our answer too.
Weekly Challenge
Do you have the skills to make it to the top? Subscribe to our weekly challenges. Try your best to solve the problem, share your solution, and see how others tackled the same problem. We share our answer too.
Unable to display your progress at this time. Please try again a little later, or contact an administrator if you continue to see this error.
Getting started with Designer? | Start your journey with our new Learning Path!

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

Asteroid

👍

Fireball

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

 

 

 

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
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.
2019-03-14 12_07_51-Alteryx Designer x64 - challenge_35_start_file_new.yxmd_.pngMy 2. go at this

 

Still Climbing

/Thomas

Meteor

Here is a solution

Alteryx Certified Partner

Solution attached (again)

 

Spoiler
challenge_35 - Snip.PNG
Meteor

A little longer but got there. 

Alteryx Certified Partner

Here is my solution.