Link in a worksheet

Linking in a worksheet allows you to perform various actions on a table and then link the results into an existing table or worksheet.

What if you wanted to modify the Product Master table and link those results into the Sales Item Detail table? You might do this because you only want to link in additional information for a subset of the data. For example, in this tutorial you only want to link in the additional product information for items in the snacks department. You can accomplish this in the 1010data Insights Platform Trillion-Row Spreadsheet (TRS) by selecting the rows corresponding to that department in the Product Master table and then using the Link operation to add the data to the Sales Item Detail table. In this example, you are working with only 35 rows of data. However, for tables with billions of rows, linking in only the additional information you need is more efficient and can result in quicker processing.

To link in a worksheet:

  1. From the menu bar, select Tools > Trillion-Row Spreadsheet.
    The Insights Platform displays the TRS window.

  2. Open the Sales Item Detail table (pub.demo.retail.item).
    TRS displays the New operation panel in the analysis pane on the left and the Sales Item Detail table in the results pane on the right.

  3. In the New operation panel, click Link.
    TRS displays the object browser in the Link tables panel.

  4. In the Suffix field at the bottom of the table browser, enter _pm.
  5. Click Link to worksheet.
    The Insights Platform opens a new TRS window used to select the worksheet you want to link into the Sales Item Detail table.

  6. In the Analyze tab of the new TRS window, browse for and select the Product Master table (pub.demo.retail.prod).
    TRS displays the New operation panel in the analysis pane on the left and the Product Master table in the results pane on the right.

  7. In the Department column of the Product Master table, right-click any of the instances of the value 22 and choose Select rows > Where Department has the value 22 from the menu.
    TRS performs the row selection and adds the select operation to step 2 in the analysis timeline.

Because the original Product Master table has been modified by the row selection, it is now considered a worksheet. The worksheet has four rows.

This worksheet can now be linked into your Sales Item Detail table in its current form.

  1. In the new TRS window, click the Minimize () icon to return to the original TRS window containing the Sales Item Detail table.
    TRS displays the link column options for the Sales Item Detail table in the Link tables panel.

Just like when you linked in the original Product Master table in Link in a table, link once more on the SKU columns.

  1. In the After linking drop-down list, select Keep all rows.
    This option is chosen because you want to link in the four selected rows from the foreign worksheet (Product Master) and retain the rows in the base table (Sales Item Detail) that do not have a matching department.
  2. Drag the Item SKU column from the Sales Item Detail section to the blank section directly to the right.
  3. Drag the SKU column from the Product Master section to the blank section directly to the left.
  4. Click the Submit operation () icon.
    TRS links the Product Master worksheet into the Sales Item Detail table.

    The result is a new worksheet that has information from the Product Master worksheet only for those item SKUs that fall under department 22. However, the other rows in the Sales Item Detail table remain, and the columns from the Product Master worksheet are left blank for items not in department 22. This is useful if you want to look at additional information about one subset of rows in a table or worksheet but still maintain the original level of detail for the other rows.

    As an alternative, what if you want to simply eliminate the rows in the Sales Item Detail table that are not applicable to the selection in the foreign worksheet (Product Master)? In that situation, you only need to change one setting. The beauty of the analysis timeline is that you can make that change very easily to obtain those results.

    The process to link and select rows is nearly identical to the process of simply linking to another table or worksheet. The only difference is the results produced. Instead of applying the information from the foreign worksheet to the entire base table or worksheet, the Include matching rows linking option retains only those rows in the base table that correspond to the selection in the foreign table.

    For instance, what if in the exercise you just completed above, you only wanted to see SKUs in the base worksheet that are in department 22? Since department information does not exist in the base table, you cannot make that selection. You could first link in the Product Master as the foreign table and then perform a row selection. However, that requires first linking in more rows than you need, which is less efficient. The best solution is to first select the rows in the Product Master table, then perform a link and select to eliminate the rows in the base table that cannot be linked.

    With this in mind, go back and edit the link operation in the timeline to perform a link and select.

  5. In the Analyze tab, click the Link to table pub.demo.retail.prod operation in step 2 of the timeline.
    TRS displays the link column options for the Sales Item Detail worksheet in the Link tables panel.

  6. In the After linking drop-down list, select Include matching rows.
    This option is chosen because you want to link in the four selected rows from the foreign worksheet (Product Master) and eliminate the rows in the base table (Sales Item Detail) that do not match.
  7. Click the Submit operation () icon.
    TRS displays the results of your selection.

    The result is a new worksheet with only the rows from the Sales Item Detail table that have SKUs in department 22, reflecting the selection from the Product Master worksheet. Only rows left in the Sales Item Detail worksheet are those that apply to the initial selection you made in the Product Master worksheet. This method allows you to use links more efficiently and effectively in your analyses.

  8. When you are finished with this tutorial, close both TRS windows.
In this tutorial, you learned how to link in a worksheet. In the first part of the exercise, only the rows that applied to the selection in the foreign worksheet had the columns linked from the foreign worksheet. In the second exercise, the rows that were not applicable to the selection in the foreign worksheet were eliminated.

The linking tutorials are intended to introduce you to the topic of linking, but it is a complex topic with many variations, which makes it very powerful. Finding ways to combine data sets and provide them with context is what the Insights Platform is all about. After reviewing these tutorials, take some time to explore tables and see if they have places where they can be joined by columns with similar information. This will help you understand how your data is interrelated, and start you down the path of deeper, more insightful analysis.