Join Data Tables
You can combine two 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 in the Reshape Data section.
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.22 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.
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. Data in the original table are updated with data in columns that have the same names.
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.