I'd like to demo Virtual Join in JMP. This is a very exciting new feature. You may have heard that JMP nows has Virtual Join that links a main data table to one or more external or auxiliary data tables. This feature enables the main data table to access data from the those data tables without physically joining the tables. Virtually joining tables saves many things like valuable computer 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.
First we you’ll need to understand the 2 ways how the data can be virtually joined.
Then I’ll run Inside of JMP it all started with 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. And 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.
Now that we have that background let’s quickly dive into an example of a virtually joined data tables using the Pizza Profiles sample data, you know I love pizza. Or maybe you don’t.
The referenced data table is linked using the the ID column contains a Link ID column property.
Please note, the key icon in the Columns panel 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.
Suppose that respondents in a pizza experiment chose their favorite crust, topping, and cheese.
First.
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.
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
Practice on your own using the Movie Rentals.jmp sample data table for another example of virtually joined data tables. The data tables are already joined with Movie Customers.jmp and Movie Inventory.jmp, so you do not need to specify the Link Reference.
Extra Virtual Join Info
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
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.
