Return all addresses for a street name or all units for a multi-unit address

[JasonR]

Hello,

I have two questions that surround addresses.

1. Is there a way to return all addresses for a given street name?

Query: Main ST

Return: 101 Main ST, 102 Main ST, 103 Main ST, etc...

2. Is there a way to return all APT/Units for a given address of a multi-unit building (Apartment, Condo, etc...)

Query: 123 Main ST aka Bob's Condos

Return: 123 Main ST Apt 1, 123 Main ST Apt 2, 123 Main ST Apt 3, etc...

I know I can verify an address as real via CASS but I want to know am I missing an address.

But is this possible?  

Thank you for your suggestions. 

Jason

[mbarone · Accepted solution]

The only way I can think of to do this is if you subscribe to the Alteryx 3rd Party Data Package "Experian Household", which is a significant extra charge, but very useful. 

Here is my configuration:

And here's some selected output from the query:

[JasonR]

Thank you so much for this solution. Yes, we are lucky enough to have the US Business Insights license that includes Experian. I was able to reproduce what you suggested. It gets me 95% the way there but I can deal with that remaining 5% mystery.

What I mean about that is:

Take for example:  9205 122ND CT NE APT 102   KIRKLAND, WA, 98033

CASS:

It’s a real address: https://www.zillow.com/homes/9205-122nd-Ct-NE-Apt-102,-Kirkland,-WA-98033_rb/58385807_zpid/

And when you validate it against CASS, the 'CASS Results' returns is ‘AS01 for both | Address matched to postal database’

However: 

Experian (utilizing Calgary tool):
Returns: Info: Calgary  Input (3): Calgary found 0 matching records out of 133709449 in ConsumerView.

What I used to query for it:

When I remove ‘Secondary Address’ & ‘Post Direction’, I get all the other condos but not Apt 102

I did add the ‘ConsumerViewDecoderOutput’ to help with Codes. Just wish CASS had one.

Yes, I have three different ways to look at the info. 

IMHO: Sometimes CASS allows two addresses for the same 'address'. Meaning, use the address above but instead of  Apt 102, use  Apt 103 and just Apt 3. It returns an AS01 but Experian only returns 'Apt 3'

This will work for my needs but if anyone has a solution that gets closer to 100%, please reply.

Again, Thank you @mbarone, this works marvelously!  

Jason

[mbarone]

No problem; maybe this will help with the CASS codes:  https://help.alteryx.com/2020.2/CASS.htm.   You can expand some of the sections and get the codes.  Attached is a spreadsheet I put together for this.  Maybe it'll help.