344340_en-US

liliconcom

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

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: 

08106
08107
08108
08109
08110
08110
08201
08202
08203

08213

08214

08215

08215

08217

08218

08219

 

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.

 

08110	34007
08201	34001
08202	34009
08203	34001
08204	34009
08205	34001
08210	34009

 

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! 

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

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.

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

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!

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

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:

https://community.jmp.com/t5/Discussions/join-tables-based-on-location/m-p/14827/highlight/true#M13786

 

let us know if it helps,

Ron