Link in a table

You can link in another 1010data Insights Platform table to the current table or worksheet.

In this tutorial, you will link the Product Master table into the Sales Item Detail table using the Trillion-Row Spreadsheet (TRS). The combination of these two tables provides you with both the product and sales information within one worksheet for items sold within your fictitious retail chain.

To link in a table:

  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.

    You will use the table browser to select the foreign table (Product Master) that you want to link into the current table (Sales Item Detail).

  4. In the Suffix field at the bottom of the table browser, enter _pm.
    While not required, this field is highly recommended as it differentiates the column names from the two tables you are linking. By adding the _pm suffix to the columns from the Product Master table, it will be clear in the final worksheet which columns came from the Sales Item Detail table and which came from the Product Master table.
  5. Browse for and select the Product Master table (pub.demo.retail.prod).
    TRS displays the link column options in the Link tables panel.

In the Link tables panel, you select at least one column in the base table (Sales Item Detail) that matches a column in the foreign table (Product Master). For example, an item in the Sales Item Detail table that has a value of 366 in the Item SKU column matches with the first row in the Product Master table that has a value of 366 in the SKU column. These columns provide a common data point where the two tables can be linked. While you can link two tables with more than one column at the same time, this tutorial focuses on linking a single column.

Columns in the Link tables panel are color coded so that you can quickly determine its data type.

Table 1. Column data type by color
Color Data Type
Red Integer

The integer data type (int) is used to represent whole numbers that can be stored within 32-bits.

Purple Big integer

The big integer data type (bigint) is used to represent whole numbers that are outside the range of the integer data type and can be stored within 64 bits.

(Available as of version 11.25)

Blue Decimal

The decimal data type (dec) is used to represent 32-bit floating point values.

Yellow Text

The text data type (text) is used to represent values that contain uppercase and lowercase letters, numbers, spaces, and symbols.

Note: When linking two tables, make sure that the columns from each table have the same data type. An error will occur if you attempt to link a column of one data type in a table to a column of a different data type in the other.

  1. In the After linking drop-down list, select Keep all rows.
    This option is selected because you want to link in all rows from the foreign table.
  2. Drag the Item SKU column from the Sales Item Detail section to the blank section directly to the right.
    This is the column from the Sales Item Detail table you currently have open.

    Note: It is important to point out that the columns in each section use the column headings from the table, not the column names. You can view the column names instead by marking the Show with column name checkbox.

Next, select a column in the foreign table you are linking in that corresponds to the one you just selected in the table you currently have open.

  1. Drag the SKU column from the Product Master section to the blank section directly to the left.
    This is the column in the Product Master table you are linking in. Again, this is the heading of the column, not the name.

  2. Click the Submit operation () icon.
    TRS links the Product Master table into the Sales Item Detail table and displays the columns from the two tables combined.

    With the columns combined, not only can you see that an item with the SKU 3B7 was purchased on 5/15/12 at store 1, but you can also see its corresponding item description (PEPSI 20 OZ) and department (SNACKS).

  3. When you are finished with this tutorial, close the TRS window.
In this tutorial, you linked the Product Master table, which had not been changed in any way, into the Sales Item Detail table. However, what if you had done some work to the Product Master table, thus making it a worksheet? In this case, you would need to link in a worksheet. For instructions, see Link in a worksheet.