Challenge #20: List Parsing

[GeneR]

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]

Great challenge, spent 45 minutes to get this done!

[TaraM]

A solution has been posted above.

スポイラ

[SeanAdams]

Painful but useful exercise!

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

スポイラ

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]

I've got a Marquee Crew solution.

Cheers,

Mark

[estherb47]

Great challenge!

[NicoleJohnson]

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.

スポイラ

[Laurap1228]

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.

スポイラ

[LordNeilLord]

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.

スポイラ

[LordNeilLord]

Workflow