community
cancel
Showing results for 
Search instead for 
Did you mean: 

Alteryx designer Discussions

Find answers, ask questions, and share expertise about Alteryx Designer.
Upgrade Alteryx Designer in 10 Steps

Debating whether or not to upgrade to the latest version of Alteryx Designer?

LEARN MORE
SOLVED

Match an address without a zip...to a zip code.

Asteroid

Hey guys,

 

We're evaluating the CASS/spatial package right now...and so far we're very impressed.

 

However, we've run into a bit of a hitch. We have a zillion addresses (number, street name, street prefix, county, and state) but what we don't have is zip codes. 

 

I ran the addresses through the CASS tool, but although CASS matched my addresses with the addresses in CASS, it didn't pull in the zip code. The CASS zip file is empty.

 

I am 99% sure the tool can add zips to addresses, but I haven't figured it out yet. Any ideas?

 

Kevin

Bolide
Bolide

CASS needs at least:

address / Zip

Address / City / State

 

it will add in missing information so long as it has the above. If it isn't working, check the CASS_Results for "AE" prefix codes and look up here - https://help.alteryx.com/11.7/index.htm#CASS_Results.htm

 

edit: another thing to note is that CASS will still clean up addresses without finding the correct address in the database. so, Drive = DR, Street = ST, etc, but it might not actually exist. - Check for AS01 or AS02 in the CASS_Results field to see that it has been verified. 

Alteryx Certified Partner
Alteryx Certified Partner

@EvolveKev,

 

It depends upon how easily you want to grab the data.  The USPS appears to have a "free" service for lookups.  I'd write a macro that performs the lookup.  I'd find other solutions potentially more work than just going to the post office and getting their answer.  

 

https://www.usps.com/business/web-tools-apis/welcome.htm

 

The terms of use state that the data must be used for shipping.  I'm no lawyer, but if you are ever planning on mailing to these addresses, then it sounds reasonable to use the API and create mailable addresses with the ZIP.  Read for yourself and see if this helps you.  What's your take?

 

Cheers,

 

Mark

Alteryx ACE & Top Community Contributor

Chaos reigns within. Repent, reflect and reboot. Order shall return.
Bolide
Bolide

the only problem i've encountered with the API is that it can be slow when trying to certify a large volume of addresses. The CASS tool is threaded and can push through a much as your computer can handle without major limitations. The API will be throttled and have plenty of latency to drive the process into the ground.  

Quasar
Quasar

I believe CASSing only standardizes the address.  Meaning "Street" becomes "St" and that sort of thing.  If it can find the address using the zip code, then it will add in delivery point and a bunch of other data points.  But if there is no zip, then unless the address is a zip in and of itself (some post offices have their own zip), you're not going to get zips.

 

But you should be able to use the Street Geocoder tool to get zips based on address alone.

Alteryx
Alteryx

Hi @EvolveKev 

 

Can you provide a small sample of a few addresses? I am able to generate ZIP codes (and even ZIP+4) for addresses missing them entirely - however I know these addresses to be correct before loading them into the CASS tool.

 

My guess is that CASS is attempting to fix other issues with the address quality first. What are the codes you are getting in the CASS_Results fields? if it references AS01 or AS02 in there then it should be able to generate a ZIP code.

Quasar
Quasar

Ah yes - when I try addresses (number, street, city, state) that I too know are 100% accurate, it will return the zip and other fields.  For a bunch of our client addresses that must not be accurate, CASS will not return any other data points (error code = Z, "Invalid ZIP/Postal Code"), but the geocoder will.

Asteroid

Hey @TravisR @mbarone @jarrod @MarqueeCrew 

 

Thanks for all of the replies - this has given me plenty of options to look at.

 

The first thing I noticed was my city field was full of fluff, so CASS couldn't match that. I'm simply going to update that field with the actual city names (I bizarrely have codes in there) and see where that gets me.

 

If that doesn't work (or even if it does), I'll let you all know how it works out.

 

 

Asteroid

Hey everyone, 

 

Looks like just adding the city sorted us out - easy! 

 

@MarqueeCrew - I love that idea. Unfortunately, we don't need to mail anyone anything - we're "simply" building some internal databases which will ultimately be used in marketing programs.

 

 

Labels