Challenge #20: List Parsing

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!

challenge_20_start_file.yxzp

challenge_20_solution.yxzp

Parse

Preparation

Advanced

Data Analysis

Transform

Difficult

Accepted answers

All comments

ShaunMcleod

Great challenge, spent 45 minutes to get this done!

TaraM

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

SeanAdams

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.

challenge_20_seanSolution.yxmd

MarqueeCrew

I've got a Marquee Crew solution.

Cheers,

Mark

challenge_20_MarqueeCrew.yxmd

estherb47

Great challenge!

challenge_20_start_file.yxmd

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.

Spoiler

challenge_20_NicoleJohnson.yxmd

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.

Spoiler

challenge_20_LP.yxzp

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.

Spoiler

LordNeilLord

Workflow

challenge_20_LNL.yxzp

patrick_digan

Spoiler

challenge_20_start_file.yxmd

dsmdavid

Done! A lot of trial and error...

solved#20_d.yxzp

samN

Found some weird discrepancies when using the regex widget vs regex match in the fomula tool. Formula Tool didn't seem to want to accept case specific parts of the formula. Anyways got around it, Nice challenge. Could probably have done it more compactly.

challenge_20.yxmd

samN

Realized the reason the formula tool was giving different results was I didn’t realize that regex match is by default case insensitive in the formula widget.

Waynemk

That was fun! what is wrong with me?

challenge_20_waynek.yxmd

ggruccio

Had to really work at this one! Surprised to see it come out so well!

challenge_20_gg_finish.yxzp

LandonG

I probably spent way more time than I needed on this challenge. Got to practice my fledgling REGEX skills though!

Spoiler

challenge_20_start_file.yxzp

philipmannering

This one was tricky. Solution attached.

Spoiler

challenge_20 - Restructure to Tabular using Regex.yxmd

jasperlch

Solution attached.

challenge_20_JL.yxzp

aclaxton

Got almost all the way there but couldn't figure out how to combine the information. Followed the Marquee Crew's method to finish it up

Spoiler

michalsicak

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

Spoiler

challenge_20_start_file.yxmd

jamielaird

Solution attached. Merry Christmas everyone :-)

Spoiler

challenge_20_solution_JL.yxzp

Suzanne

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

challenge_20_start_file_Suzanne.yxmd

Natasha

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

challenge_20_Natasha.yxmd

MaxGiegerich

Ooft. Death by parsing.

MG Weekly Challenge 20 - List Parsing.yxmd

JoshKushner

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

challenge_20_start_file.yxzp

JosephSerpis

Challenge Completed

Challenge_20_Joe_Serpis.yxmd

mceleavey

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:

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:

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.