Help mapping values in lookup table to records in a separate table

Question

Hello,

I've been unable to accomplish the following task with joins, unions, and the find a replace function.

Task

Table 1 contains 70, 000 records of customer orders and each record contains a zipcode with other fields (multiple records can have the same zipcode while all the other fields will be different)

example of Table 1 records (72,000 total records):

Customer #Order #Zip code00011000130071000210002300870003100033007100041000430051

Table 2 contains 50,000 records and each records contains just a zipcode, FIPS (Federal Information Processing Standards) code, and a Rural/Urban Code (i.e. "1"= rural area with pop less than 20K, "2" = metro area with pop greater than 100K) The zip codes in table 2 are unique, but multiple zipcodes can have the same Rural/Urban code (i.e. zipcodes 30071, 30087, and 30067 have the Rural/Urban code of "1"

Example of Table 2 records (50,000 total records)

ZipcodeFIPSRural/Urbanl Code300710087130087008213007700812

What I want is

Table 1

Customer #  Order #Zip codeRural/ Urban code0001

10001300711000210002300871000310003300711000410004300772

What I get is this (using "find and replace" with the "entire field" value checked and "Append Fields to Record")

Customer #Order #Zip codeRural/ Urban code000110001300711000210002300871000310003300711000410004300771

The mapping of Zip codes to Rural/Urban code in table 1 is incorrect. The Rural/Urban codes range in values from 1-9, but when I use the find and replace function not all the values are used. The total number of records (72,000) is correct but the  Zip codes to Rural/Urban mapping is wrong.

When I use the join and union function the mapping is correct but the number of records is wrong. After I run the "join" and "union" function I get a total of 115,000 records instead of the original 72,000. I've attached a copy of the Rural/Urban code lookup table. The correct tab is FIPS  to Zips. Please let me know what I should do.

Rural UrbanCodes to Zipcodes.xlsx

MSalvage · Accepted Answer

@gth639b,

Do you have a hierarchy you are supposed to follow? If so I would just go by that.

If it is important to have all the rural/urban codes you can do some manipulation to make them separate fields(attached example). This would allow you to keep all the data but still end with your 72,000 rows.

Not Totally sure what the goal is but I hope this has helped.

Best,

MSalvage

gth639b · Accepted Answer

MSalvage,

Thank you this has been a tremendous help. The end goal is to try to predict whether an automobile part will be delivered late or not using logistic regression. I believe the urban/rural value of a customer impacts lateness which is why I wanted to included it as an extra field to run the regression on.