cancel
Showing results for 
Show  only  | Search instead for 
Did you mean: 
JMP is taking Discovery online, April 16 and 18. Register today and join us for interactive sessions featuring popular presentation topics, networking, and discussions with the experts.
Choose Language Hide Translation Bar
jstewart
Level I

Creating a formula for a table from a different JMP table

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 (Alumni) ms
Super User (Alumni)

Creating a formula for a table from a different JMP table

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)

);

View solution in original post

2 REPLIES 2
ms
Super User (Alumni) ms
Super User (Alumni)

Creating a formula for a table from a different JMP table

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
Level I

Creating a formula for a table from a different JMP table

Thanks!