Subscribe Bookmark RSS Feed

Creating a formula for a table from a different JMP table

jstewart

Community Trekker

Joined:

Jan 10, 2012

Hello,

I am trying to create a forumla for a column in a table using data from a different JMP table.

I have a large database with over 1 million patients admitted to various hospitals.  The database contains their Zip Code.  I would like to create a new column (RUCA Code).  I would like to fill that column with the corresponding RUCA code which I have in a different JMP file (2nd table below).  I would rather not have to input each Zip code and RUCA code into the formula by hand since there are around 5000 zip codes in the file.  It does not have to be a dynamic formula as the database is complete, and I would prefer it is not dynamic formula since those seem to take up more memory.

IDZip CodeRUCA Code
11232150011.0
11233150042.0
11234150071.0
11235150181.0
11236153347.0

ZIPNRUCA2.0
150011.0
150031.0
150042.0
150051.0


Any help would be appreciated.

Thanks.

1 ACCEPTED SOLUTION

Accepted Solutions
ms

Super User

Joined:

Jun 23, 2011

Solution

I am not sure what you mean with "formula" here. To me it seems as task of joining the tables by matching the zip codes.

The Update function useful for such a task.

Manually:

Activate the first table and invoke Update from the Tables menu. Slect the second Table in the list and check "Match Columns". Then select Zip Code and ZIPN as matching columns and hit OK. If second table contains other columns that you don't want to be added to the 1st table you can select only the ones yo need.

By script:

dt=datatable("1st Table");

dt << Update(

          With( Data Table( "2nd Table" ) ),

          Match Columns(:Name("Zip Code") = :ZIPN)

);

2 REPLIES
ms

Super User

Joined:

Jun 23, 2011

Solution

I am not sure what you mean with "formula" here. To me it seems as task of joining the tables by matching the zip codes.

The Update function useful for such a task.

Manually:

Activate the first table and invoke Update from the Tables menu. Slect the second Table in the list and check "Match Columns". Then select Zip Code and ZIPN as matching columns and hit OK. If second table contains other columns that you don't want to be added to the 1st table you can select only the ones yo need.

By script:

dt=datatable("1st Table");

dt << Update(

          With( Data Table( "2nd Table" ) ),

          Match Columns(:Name("Zip Code") = :ZIPN)

);

jstewart

Community Trekker

Joined:

Jan 10, 2012

Thanks!