Is it possible to have JMP recognize a value and fill in a new value

Question

I'm wondering if someone could help me figure out a JMP script for this or an automated way to do this.

I have a set of ZIP codes that I want JMP to recognize and fill in a new column with the corresponding county-level code. I'm not sure how best to describe this or if there's a term for this. I guess I want JMP to "crosswalk" ZIP codes and convert them to County-level codes.

As an example, in one file, I have these ZIP codes as data:

081060810708108081090811008110082010820208203

08213082140821508215082170821808219

I want JMP to look through each ZIP code and produce in a new column of the corresponding county code. In a separate file, I have the corresponding zip--> county codes.

0811034007082013400108202340090820334001082043400908205340010821034009

Other than doing this manually by hand, where I'd search each ZIP code to find out what the county code is, is there an automated way to do this?

Thanks for your help!

ron_horne · Answer

Hi @SeaAardvark ,

I didn't understand if you have the two matching columns in a another table. If so, i would use tables join or update once and then script it.

If you have the data on the map (no key matching table) there is also solution for that. In the past, we were discussing a location based update of tables: join tables based on location

let us know if it helps,

Ron

Craige_Hales · Answer

Here's what it would look like with a virtual join (point and click, no jsl required)

the gold key values must be unique in the column

The county[zip] column is hidden by default, I un-hid it in the picture. It works best with the tables already saved, and may need the gold-key table opened before the blue-key table (you'll know if the key is grayed.)

edit - you might want to keep the codes as character data, I just noticed the leading zeros are gone!

Craige_Hales · Answer

Use an associative array to make the lookup table, something like (untested code follows, dtZtoC is your table of zip to county info)

lookup = associativearray();
for( i = 1, i <= nrows(dtZtoC), i += 1,
   lookup[ dtZtoC:zip[i] ] = dtZtoC:county[i];
);

then use a loop or a column formula to make the new column (untested...this is how the column formula for county might look)

lookup[zip]

The column formula depends on the lookup table which won't be available later, so remove the formula from the column when done. Or, use a loop, something like this, without a column formula (untested...)

for( i = 1, i <= nrows(dtData), i += 1,
   dtData:county[i] = lookup[dtData:zip[i]];
);

edit:  Reverse Geocode Lat-Lon to Zip uses a virtual join; a virtual join might be a good choice here as well. The virtual join adds complexity to get flexibility; if a zip to county mapping changes, just update the dtZtoC table and the dtData table automatically gets the change. But now you have two tables that are needed.