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 #20: List Parsing

GeneR
Alteryx Alumni (Retired)

The link to last week’s challenge (challenge #19) is HERE.  For this week’s challenge we need to clean up an unformatted text file with unstructured data.

 

Use Case: A sales executive got a series of leads from the DMA conference. Unfortunately it’s a text file that we want to restructure into a tabular form to load into Salesforce.

 

The result should be a table formatted the same as the output sample.

 

As always there are many ways to approach the same problem in Alteryx.  I am looking forward hear about some of your solutions.

 

Enjoy!

ShaunMcleod
5 - Atom

Great challenge, spent 45 minutes to get this done!

TaraM
Alteryx Alumni (Retired)

A solution has been posted above.

Spoiler
2016-04-11 09_56_10-Alteryx Designer x64 - Week_20_DMA_List_Parsing_Advanced_Solution.yxmd.png
Tara McCoy
SeanAdams
17 - Castor
17 - Castor

Painful but useful exercise!

 

Again, I went the long way around, compared to @TaraM & @GeneR - notes and solution below.

Spoiler
While the provided solution assumes a company name if it does't match any of the other criteria, the attached looks for a specific pattern to find company names.
Same as provided - it identifies fax numbers; telephone numbers; addresses and websites - and flags these
Then by elimination - anything else is either a company or a notes field.

Split them all out the hard way (using discrete filters), and then a multi-join to bring them back together

the provided solution is far more elegant.
MarqueeCrew
20 - Arcturus
20 - Arcturus

I've got a Marquee Crew solution.

 

Cheers,

Mark

Alteryx ACE & Top Community Contributor

Chaos reigns within. Repent, reflect and restart. Order shall return.
Please Subscribe to my youTube channel.
estherb47
15 - Aurora
15 - Aurora

Great challenge!

NicoleJohnson
ACE Emeritus
ACE Emeritus

This one was BRUTAL for me. I will not admit how long I've been battling this one... but I can now safely say that RegEx and I are close friends now, both in tool and formula form, and that I now have a deep-seated dislike for non-standard formatted addresses and phone numbers...

Yikes.

 

Spoiler
WeeklyChallenge20.JPG
Laurap1228
11 - Bolide

This challenge was difficult for me. It really highlighted the importance of testing your output vs. the given output. That's how I found the "RFI     ." and the two phone numbers starting with 1- that I missed during my initial data inspection. I added in a testing section to the end of my workflow if anyone has a more efficient way to test the two outputs are identical I welcome feedback.

Spoiler
challenge20.jpg
LordNeilLord
15 - Aurora

This one was a real head scratcher, it took a lot longer than I thought it would! I had to peek at the solution to get some help parsing some of the addresses.

 

Spoiler
Weekly Challenge 20.png
LordNeilLord
15 - Aurora

Workflow