Weekly Challenge

Solve the challenge, share your solution and summit the ranks of our Community!
IDEAS WANTED

We're actively looking for ideas on how to improve Weekly Challenges and would love to hear what you think!

Submit Feedback
We've recently made an accessibility improvement to the community and therefore posts without any content are no longer allowed. Please use the spoiler feature or add a short message in the message body in order to submit your weekly challenge.

Challenge #20: List Parsing

Highlighted
Alteryx Certified Partner

phew, almost gave up a couple of times and definitely solved this in a very complicated and error-prone manner.

 

Spoiler
challenge20.png
Highlighted
Alteryx Certified Partner

Solution attached. Merry Christmas everyone :-)

 

Spoiler
Screen Shot 2017-12-25 at 12.30.42.png
Highlighted
7 - Meteor

It's a bit messy but good learning so left some of the trials and tribulations in the final solution -:)

Highlighted
Alteryx Certified Partner

It took me quite a lot of time to do this one. Was trying to match through the company name position between blanks/nulls, but that was all very inconsistent. Finally gave up and did it with regex as everyone else.

Spoiler
Screen Shot 2017-12-26 at 22.15.11.png
Highlighted
Alteryx Certified Partner

Ooft. Death by parsing.

Highlighted
12 - Quasar

By far took me the longest of any, but after understanding there was the address/phone combo it worked out.

Highlighted
Alteryx Certified Partner
Alteryx Certified Partner

Challenge Completed

Highlighted
Alteryx Certified Partner

A beautiful example of why Regex is the best thing since Otto Frederick Rohwedder embraced his powerful laziness and made an paralleled contribution to society.

I suspect my solution differs slightly to most in form if not logic:

 

Spoiler
I first replaced the solitary "." fields, then individually isolated the Fax, Phone, Website and Notes fields using Regex:
Regex.PNG

Fax.PNG
I then transposed the data to create a single value column with these fields and used the Find and Replace tool to remove them from Field_1. This left Field_1 containing only Company Name and Address:
Transpose.PNG
I then used Regex to create the Address field and a simple replace formula to isolate the company name.
It was then a simple method of creating a record id and summarizing using the record id and company name as the group and the longest values of each column as the values.
Workflow.PNG


Regex makes me happy.

 

Highlighted
Alteryx Certified Partner

Quite difficult. Peaked into other solutions posted

Highlighted
Alteryx Certified Partner

Solution attached - thanks!