Creating a formula for a table from a different JMP table

[#1]

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.

ID	Zip Code	RUCA Code
11232	15001	1.0
11233	15004	2.0
11234	15007	1.0
11235	15018	1.0
11236	15334	7.0

ZIPN	RUCA2.0
15001	1.0
15003	1.0
15004	2.0
15005	1.0

Any help would be appreciated.

Thanks.

[#2 · Accepted 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)

);

[#3]

Thanks!