Linking worksheets allows you to perform various actions on two tables and then link the results.
To link in a worksheet to the current table or worksheet:
- In an open table or worksheet, click
The Link in Another Worksheet dialog is presented.
- From the list of open worksheets displayed in the Link in Another Worksheet dialog, select the foreign worksheet. Note: When you link to a worksheet (a table with operations applied to it), the full worksheet is pulled into memory, so there is a limit on how large the worksheet can be (roughly about 250,000,000 cells). When you link to a table, no such restriction applies. See the section on Linking under Writing Efficient Queries for tips on how to help limit the size of the worksheet you're linking in.
- From the first set of drop-down lists, which correspond to the current table or worksheet, select the column(s) you would like to link on. Note: To change the number of linking columns that appear in the Link in Another Worksheet dialog, click and change the Number of Columns for Links setting under the User Interface section.
- From the second set of drop-down lists, which correspond to the foreign worksheet, select the column(s) you would like to match to those specified in the previous step.
- Enter a Suffix. (optional)
The suffix may be any alphanumeric value and may contain underscores. In the resultant table, the suffix will be appended to the column names of the foreign worksheet, which you are linking in, and is useful when linking worksheets that contain similarly-named columns.
For example, if you are viewing your Sales Item Detail table and want to link in your Product Master worksheet, you can append a suffix of _PM to the columns from Product Master in the resultant table:In this example, a column named class in the Product Master worksheet is named class_PM in the resultant table. Any reference to these columns (e.g., when creating a computed column) would use the column names with the suffix.Note: In the above example, column names are used for the column headings. This can be set using the Show column headings as preference under the User Preferences section of the Set Preferences dialog.
- Enter a Label. (optional) In the resultant table, the label will appear at the top of those columns from the foreign worksheet, which you are linking in.Note: You can see the label in the column headings when the Show column headings as preference is set to the label or both the name and label. This can be set under the User Preferences section of the Set Preferences dialog. In the example below, column labels are used for the column headings.
As in the example in the previous step, if you are viewing your Sales Item Detail table and want to link in your Product Master worksheet, you can add the label of "Product Master" to its columns in the resultant table:
In this example, the last three columns are from the Product Master worksheet, as denoted by the "Product Master" label above each of them.
- If you would like to enforce a less strict way of linking the foreign worksheet to the current table, select As-of link? and enter a Row Shift value. (optional)
Using as-of links, you can match a row in the current table with a row in the foreign worksheet whose value is the closest match less than or equal to the value in the current table. The Row Shift value allows you to shift the match that number of rows following or preceding the closest match.
- Click Finish.
The foreign worksheet is linked in to the current table.
The result of a link is a dynamic table that may be manipulated just like any other table. You may select rows, sort rows, rearrange columns, create computed columns, link in other tables, perform summarizations or tabulations, or apply any other kind of analysis.