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!

Alteryx Designer Desktop Discussions

Find answers, ask questions, and share expertise about Alteryx Designer Desktop and Intelligence Suite.
SOLVED

Help Address scrubbing and parsing

lruchlin
7 - Meteor

Hello All,

 

I am having some trouble scrubbing and parsing some address fields. Is there a standard or good process to do this?

 

Attached is a sample spreadsheet with Addresses. Some of the addresses are split out by column and some are not

Group_id

MLINE1

MLINE2

MLINE3

MCITY

MSTATE

MZIP

 

Below are some requirements for address scrubbing.

 

  1. If there are multiple addresses or PO boxes across the row, append these into separate address rows.
  2. Once step 1 is done, then scrub and parse the data in the columns as follows per these rules. The below table will show you what types of data should be in what column

 

MLINE1

MLINE2

MLINE3

Street address

Suite

C/O

PO box

Apt

T/A

Rural Route

Building

Attention

State Route

Floor

D/B/A

 

 

Correspondence

 

 

 

 

 

 

 

  1. All common elements on any address field should be condensed

Examples:

Street to ST

Circle to CIR

Road to RD

Avenue AVE

Lane to LN

Suite to STE

Floor to FL

 

  1. Any records that do not match would have to be split out into a excel spreadsheet so we could do manual scrubbing.

 

 

Thanks,

Lloyd Ruchlin

 

1 REPLY 1
scottj
Alteryx Alumni (Retired)

I approached this by transposing all the data by the group, then using some formula/multi row formulas to create the multiple rows.  Once the data was in a good state, I used the Address Parse tool to separate all the pieces.  See attached for my example and a place to start.

 

CleanParseAddress.png

Scott Jones
Strategic Sales Engineer
Alteryx, Inc.
Labels