Using JMP • Reshape Data • Virtually Join Data Tables
Previous
 • 
Next
Virtually Join Data Tables
Virtual Join links a main data table to one or more auxiliary data tables. This feature enables the main data table to access data from the auxiliary data tables without physically joining the tables. Virtually joining tables saves memory, because the same data are not replicated in every table that references them. And updating linked data is simpler; linked data can be independently updated in the source table without being updated in the referencing table.
The Link ID and Link Reference column properties make the linking possible.
•
The Link ID column property marks a column in the auxiliary data table as the ID column. That is, the rows of the data table are uniquely identified by the values of the ID column. The data table that has a Link ID column property is referred to as the referenced data table.
•
The Link Reference column property maps a column in the main data table to the ID column in the referenced data table. The column property specifies the path name of the referenced data table. The column that has a link reference is referred to as the referencing column. The referencing column can look up the data of the auxiliary data tables through the ID column.
Virtually Joined Data Tables shows an example of virtually joined data tables. Pizza Profiles.jmp is the referenced data table because the ID column contains a Link ID column property. The ID column contains unique values that correspond to data in the referencing data table, Pizza Responses.jmp.
Virtually Joined Data Tables
Note: The key icon in the Columns panel (in Pizza Responses.jmp above) is blue when the columns are linked and the referenced columns appear in the Columns panel. If the key icon is gray, verify your link references in the Column Info window.
With the link column properties set up, all columns from the referenced data tables become part of your main data table. Therefore, if the main data table has a referencing column, the columns of the referenced table automatically appear in the Select Columns list of the launch window.
You can prevent columns of the referenced table from appearing in the main data table by excluding them from the referenced table. Exclude columns by right-clicking them in the Columns panel and selecting Exclude/Unexclude.
Example of Virtually Joining Data Tables
Suppose that respondents in a pizza experiment chose their favorite crust, topping, and cheese. To see which cheese the respondents preferred, follow these steps:
1.
Select Help > Sample Data Library and open Pizza Profiles.jmp and Pizza Responses.jmp.
2.
First, right-click the ID column in Pizza Profiles.jmp and select Link ID.
This column contains unique values that correspond to values in Pizza Responses.jmp. For example, row one in Pizza Profiles.jmp indicates that pizza with thick crust, mozzarella cheese, and pepperoni toppings is nicknamed ThickOni. When all responses in Pizza Responses.jmp are “ThickOni”, the subject chose pizza with these attributes.
3.
In Pizza Responses.jmp, select the three columns that begin with “Choice”.
4.
Right-click and select Link Reference > Pizza Profiles.jmp.
The selected columns are linked to the ID column in Pizza Profiles.jmp and appear in the main data table’s Columns list (Virtually Joined Data Tables).
Note: Look at one of the column’s Link Reference properties again and notice that the Pizza Profiles.jmp data table is selected. See Virtual Join Properties in The Column Info Window for information about changing the link.
5.
In Pizza Responses.jmp, select Analyze > Distribution.
6.
Scroll down to select the three Cheese columns in the referenced column groups, click Y, Columns, and click OK.
Distribution Launch Window
More respondents chose Mozzarella in the Choice column. In Choice1 and Choice2, the proportion of Mozzarella to Jack is nearly even.
Distribution of Pizza Responses
Notes: 
•
See the Movie Rentals.jmp sample data table for another example of virtually joined data tables. The data are already joined with Movie Customers.jmp and Movie Inventory.jmp, so you do not need to specify the Link Reference.
•
A data table can have only one Link ID column property. The Link ID column cannot have duplicate values.
•
A data table can have multiple referencing columns. The columns can reference different or the same data tables.
•
A referenced data table can have a referencing column (a column that references another data table).
•
If the Link Reference column property is removed from the column, or the referenced data table is closed, the corresponding referenced columns are removed from the main data table.
•
The data types of the linked columns must match.
•
The Expression and Row State data types do not support the Link Reference or Link ID column properties.
•
When the main data table is saved, the referenced columns are not saved. The data tables are relinked when you open them.
•
When a referenced data table is renamed, the corresponding Link Reference column property is automatically updated.
•
Referenced columns are not included when you select Copy Table Script from the Table panel red triangle menu.
Tips: 
•
In addition to right-clicking a column to add the Link ID and Link Reference column properties, you can add the properties through the Column Info window. See Virtual Join Properties in The Column Info Window for details.
•
To share the data with another user, you might want to merge the data so that the data tables are permanently joined. This option is helpful because you provide one data table, not the main and auxiliary data tables. To merge the joined columns into the table that has a link reference, select Merge Referenced Data from the red triangle menu in the data table’s left pane. The data are replicated from the auxiliary data table to the main data table. When you save the data table, the actual data are also saved.

Previous
 • 
Next
Help created on 9/19/2017