topic Creating a formula for a table from a different JMP table in Discussions

Creating a formula for a table from a different JMP table

jstewart — Thu, 26 Jan 2012 16:08:27 GMT

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.

Creating a formula for a table from a different JMP table

ms — Thu, 26 Jan 2012 19:52:57 GMT

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)

);

Creating a formula for a table from a different JMP table

jstewart — Fri, 03 Feb 2012 18:44:41 GMT

Thanks!