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

Find Replace/Join based on Min and Max Zip code

Lucasvital
7 - Meteor

Hi,

 

I have the zip code field in my excel file A that I want match (either find and replace, or join) to a specific field in excel file B based on zip code; however, zip codes in file B are based on a range of Min and Max (split in two columns). Any tips on easiest way to solve? I've been trying to create some if formulas based on zip code values but might exist an easier solution than that.

 

Thanks

5 REPLIES 5
MichalM
Alteryx
Alteryx

@Lucasvital 

 

Could you provide an example? A sample input and desired output?

Lucasvital
7 - Meteor

Sure...

 

1- Example:

Sample Input A: 

Field Id - 123 

Country - United States

State - Florida

Field Zip code - 00080

 

 

Sample Input B

Field Name - abcabc

Country - United States

State - Florida

Field zip Code Max - 00100

Field Zip code Min - 00000

 

Output:

Field Id 123

Field Name - abcabc

Field Zip code - 00080

Country - United States

State - Florida

 

2- In case it's possible to go to next level of bringing the city name based on zip code:

Output:

Field Id 123

Field Name - abcabc

Field Zip code - 00080

Country - United States

State - Florida

City - zzzzz

MichalM
Alteryx
Alteryx

@Lucasvital 

 

I'm assuming here so please correct me if I'm wrong - if we increment by 1 from Min (0) to Max (100) - we'll get all the Florida Zip codes, correct? If that's the case, we can use the Generate Rows tool to achieve this.

 

generate-zip.png

 

The result will be a set of numbers from 0 to 100 which then need to be padded on left with 0 to complete the full Zip - we achieve that using the formula tool.

 

PadLeft(ToString([Zip]), 5, "0")

 

Once ready, we can use the Join tool to append the Field Name based on a matching Zip.

 

min-max-zip.png

 

Example attached.

Lucasvital
7 - Meteor

Hi Michal

Thank you very much - I have neither used "Generate Rows" function nor "PadLeft" as formula before - this definitely solves for US, Germany and other countries from which the ZIp Codes are only numerical (most of my database), but not for UK and Canada as the formats for zip codes are the following:

 

Non-Matched zip codes:

UK/London 6 or more digits (Mix of Integers and strings)
e.g. Low: BR0 0AA vs High: BR9 9ZZ

 

Canada - 7 digits (Mix of 3 Integers, 3 strings and one blank)

e.g. Low: R0A 0A0 vs High: R9Z 9Z9

 

Any thoughts?

 

Thanks again!

MichalM
Alteryx
Alteryx

@Lucasvital 

You could potentially invent some logic to increment these but I think it would make more sense to introduce a lookup table with a postcode - state / postcode - city mapping and use that instead.

 

Labels