For the latest version of JMP Help, visit JMP.com/help.


Using JMP > Reshape Your Data > Join Data Tables
Publication date: 06/21/2023

Join Data Tables

You can combine two JMP data tables into one new table by selecting Tables > Join. For an overall description of how to join two data tables, see To join two data tables into a new data table, follow these steps:. Tables can be joined in three different ways:

By combining them according to row number. See Example of Joining by Row Number.

In a Cartesian fashion, where you form a new table consisting of all possible combinations of the rows from two original tables. See Examples of a Cartesian Join.

By matching the values in one or more columns that exist in both data tables, or in a single data table. See Examples of Joining By Matching Columns.

Note: The JMP Query Builder option in the Tables menu provides an option to query data before performing a simple join. See Query and Join Data Tables with JMP Query Builder.

To join two data tables into a new data table, follow these steps:

1. Open the two data tables that you want to join.

2. Select Tables > Join.

In the window that appears, the names of all open tables appear below Join...with.

Figure 6.20 The Join Window 

The Join Window

3. In the Join...with box, select the table to join with the active table.

4. From the Matching Specification area, select the option that specifies how to join the tables.

5. Enter the name of the new table in the text box beside Output table name.

6. (Optional) Customize the join procedure further using the additional options.

7. Click OK to create the joined data table.

Note: Joining on matching columns that have value labels correctly joins with the column’s data values, not the value labels.

Join Options

Keep dialog open

Keeps the Join window open after you click OK.

Save Script to Source Table

Saves a script to the original data table that enables you to join the data again using the same settings.

Preserve main table order

Maintains the order of the original data table in the joined table, instead of sorting by the matching columns. Selected by default.

Update main table with data from second table

Data in the original table are updated with data in columns that have the same names. The results are displayed in a new data table.

Note the following:

JMP does not replace data with missing values.

The output table uses the same columns as the original table. Thus, when you use Update main table with data from second table, Select Columns for joined table is not applicable.

The Update main table with data from second table option is available only when joining by row number or by matching columns.

Merge same name columns

Data from the second table replaces the data of the same name columns in the original table. Note that missing values in the first table are replaced by nonmissing values in the second.

If you are matching by column, Match Flag is selected when you select Merge same name columns. The new joined table contains a nominal column named Match Flag:

If Main appears in this column, the data originated from the first (active) table.

If With appears in this column, the data originated from the second table.

If Both appears in this column, the data was found in both the first and second tables.

Match flag

Determines whether the Match Flag column is created when you are matching by column. Selected automatically when you select Merge same name columns.

Copy formula (Main Table and Second Table)

Includes formulas from the main table and/or the second table in the output columns. Selected by default.

Suppress formula evaluation (Main Table and Second Table)

Prevents JMP from evaluating columns’ formulas during the creation of the new table. Selected by default.

By Matching Columns

To join rows, select columns in both tables whose values and data types match. Follow these steps:

1. Highlight a column name from each list in the Source Columns area. The first highlighted column in the top list pairs with the first highlighted column in the bottom list, the second columns are paired, and so on. Rows join only if values and data types match for all the column pairs.

2. Click Match. The selected pair of columns appears in the Match columns box. Matching columns do not have to have the same names and do not have to be in the same relative column position in both tables.

3. (Optional) To include only the first match found, check the boxes associated with Drop multiples in both tables. Only the first match found is written to the new table. If you specify this option for one table, the first match value is joined with all matches in the other table. If you do not check the boxes associated with Drop multiples in either table, a Cartesian join is performed within each group of matching column values.

4. (Optional) To include all rows from the data table, even when there is no matching value, check the boxes associated with Include non-matches. You can specify this option for either or both data tables being joined.

By Row Number

Joins the two tables side by side.

Cartesian Join

Joins two tables using a Cartesian fashion, where it forms a new table consisting of all possible combinations of the rows from two original tables. JMP crosses the data in the first table with the data in the second to display all combinations of the values in each set.

Select Columns for joined table

Selects a subset of columns from either table for inclusion in the output table. Follow these steps:

1. In the Source Columns area, highlight the columns from each table that you want to include in the new table.

2. Click Select in the Output Columns area.

Output table name

Specifies the name of the joined table.

Auto Refresh

Automatically refreshes the table Preview. If the Preview takes too much time to complete, you can deselect this option and click the Refresh button Image shown here. Selected by default.

Preview with Random Subset

Select this option for very large tables that can cause delays as the Preview pane is refreshed. Deselected by default.

Want more information? Have questions? Get answers in the JMP User Community (community.jmp.com).