Linking tables

Linked Views is a feature in Deephaven that enables a user to interactively filter the content in one Deephaven table or plot based on the content selected in another Deephaven table.

To use Linked Views among tables or plots in the Deephaven console, the objects must first be "associated" to each other. One table is the source table and the other table is the target table or plot. When linking tables, a filter is applied to define the column in each table that should be linked. When a row in the source table is double clicked, the respective columns in the target table are filtered according to the filter parameters assigned during the linking process. Similarly, when a plot becomes the target of a link, the plot will instantly rechart its data according to the row clicked in the source table.

Note

See also: Linking Plots

Make Link To / From

To link two tables in the Deephaven console, both tables must be open in your workspace. They can be in different panels or in multiple tabbed panel sets, but they must be open and available in the console.

The examples shown below use data from the LearnDeephaven namespace. The following query can be used to recreate these tables in your own console:

StockTrades=db.t("LearnDeephaven", "StockTrades").where("Date=`2017-08-25`")
StockQuotes=db.t("LearnDeephaven", "StockQuotes").where("Date=`2017-08-25`")
EODTrades=db.t("LearnDeephaven","EODTrades").where("ImportDate=`2017-11-01`")

from deephaven import *

StockTrades=db.t("LearnDeephaven", "StockTrades").where("Date=`2017-08-25`")
StockQuotes=db.t("LearnDeephaven", "StockQuotes").where("Date=`2017-08-25`")
EODTrades=db.t("LearnDeephaven","EODTrades").where("ImportDate=`2017-11-01`")

To initiate the linking process, right-click anywhere within the body of a table. The drop-down menu then presents options for Make Link To and Make Link From:

img

When the Make Link To option is selected, the table initially selected becomes the "source" table and the cursor will change to a link icon with a down arrow (img ).

Navigate to the target table in the Deephaven console and click anywhere in the body of the table to designate it as the "target" table. When you click in the target table, the Filter Editor will open as shown below. The background colors of the tables' tabs will change to signify the source and the target.

img

In this example, StockTrades (at left, green) is the source table, and StockQuotes (at right, magenta) is the target table.

When the Make Link From option is selected, the first table selected becomes the "target" table, the cursor changes to a link icon with an up arrow (img ), and the second table becomes the "source" table. However, the same Filter Editor opens after clicking the second table.

Filter Editor

The Filter Editor window is used to assign the respective columns in each table that should be linked and how they should be filtered.

Click the + button located at the bottom left corner of the window to create a new filter. A new row will be added to the panel.

To change Filter Editor the column names, double-click a cell. You can then choose from the available options for Target Column, Operation and Source Column. Typing the first letter of a column name will cycle through matching columns; for example, typing "S" might cycle through Sym, Size, and SaleCondition.

Any column in each pair of tables may be linked, but the filter condition selected must be applicable to the data types found in both columns.

For example, if you wanted to link the tables based on the selection of one particular USym, you would select the USym column under the Target Column(s) heading and then select the USym column under the Source Column(s). Finally, select the equals option in the drop-down menu under Operation. These parameters are shown below.

img

Multiple columns can be linked between a pair of tables. To add another filter set, click the + button, then select the columns and the filter condition. In the following example, the Last column is selected in the source table, the Bid column is selected in the target table, and a less than filter is selected for the Operation:

img

The combination of these two sets of parameters filter the target table to show content only when (a) the values in the USym columns of both tables match and (b) the value in the Bid column of the target table is greater than the value in the Last column of the source table.

Once you have added all your filter conditions, complete the linking process by clicking OK.

  • The tab for a source table will show the link icon with an arrow pointing down. img
  • The tab for a target table will show a link icon with an arrow pointing up. img
  • If a table is used as both a source table and a target table, the link icon will have one up arrow and one down arrow. img

Attempt to Match Columns

The following features may be turned on or off using the Attempt to Match Columns checkbox:

  • Link Column Matching - When you create a new link, the dialog will attempt to set the pull down menus so that the Source column is the same as the first match in the Target columns. If no match is found, they default to the first value of each.
  • Match Source Column to Target Column/Match Target Column to Source Column - When you change the Source or Target column, the dialog will attempt to set the corresponding column to match. In other words, if the Source is set to USym, the Target column will automatically change to USym, and vice versa. If no match is found, it is left alone.

Your on/off selection is saved in the workspace, and all dialogs that open in a new Deephaven session with remain in the last used state.

The Autopopulate Links button will populate the Linked View Filter Editor with matching sets of source and target columns, as shown below:

img

Filtering the target table

When a row in the source table is double-clicked, the target table is filtered based on the parameters specified and the value(s) in the row of the source table that was double-clicked.

For example, the highlighted row in the StockTrades (source) table below has AAPL as the USym value and 159.7400 as the value in the Last column. When that row is double-clicked, the StockQuotes (target) table is immediately filtered to show (a) only those rows with AAPL in the USym column and (b) only those rows where the value in the Bid column is greater than 159.7400.

img

If we click on a row with a different USym in the source table, the target table will then be filtered again. For example, the highlighted row in the StockTrades table below has PFE as the USym value and 33.3300 as the value in the Last column. When that row is double-clicked, the StockQuotes table is immediately filtered to show (a) only those rows with PFE in the USym column and (b) only those rows when the value in the Bid column is greater than 33.3300.

img

Linking multiple tables

The Linked Views feature enables users to link multiple tables. However, each pair of tables (source and target) is linked individually.

Any table in the Deephaven console can be the source or target for any other table as long as there are no conflicts in the filter parameters selected for each table pair.

Careful attention needs to be taken when linking multiple tables. If certain column values have already been filtered out of a table, those values will not be available in subsequent targeted tables.

Also, if a column is deleted or renamed in a linked table, an error message will appear when the source table is double-clicked. If you select Edit Links for a table pair that includes a filter for a column that has been renamed or deleted, the Filter Editor panel will highlight the row containing the missing/changed column name. For example, in the screenshot below, the column name (Volume2x) for the source table is missing:

img

Roll-up linking behaviors

Roll-ups only allow their grouping columns to participate in linking to and from other tables. When operating as a link source rollups will only provide filters for columns that are valid at the level of the clicked row.

In other words, if there are three grouping columns (meaning the table is three layers deep) clicking on a row at the second level will only result in filters from the first two grouping columns:

img

Clicking on a row in the first level only results in a filter of the first grouping column:

img

When a table is linked to another table, right-clicking in the body of either table body presents two additional options in the context menu:

img

  • Remove All Links removes all links from the selected table to any other table(s). If any columns in the table have been previously filtered, those filters will remain activated. Note: closing the table in the console also automatically removes any links.
  • Edit Links opens a dialog window that presents the filter parameters associated with the table selected. Filter parameters can be changed, added or deleted. If the selected table is linked to multiple other tables, tabs at the top of the window can be used to view/edit those other linked table filter parameters. The content in each tabbed panel is color coded to match the respective linked tables. img
    • You can choose to Only Show Broken Links using the checkbox option.
    • Selecting the Replace Filters on Link Update checkbox will clear all existing filters on Link Targets before applying applying filters from a link update. For example, let's say you have two sources linked to one target, and each source has different filter parameters configured. Filters from a previous link event from Source 1 would be cleared from the target before applying filters from a link event from Source 2.

There may be cases when your links become "broken" - for example, if columns in the table have changed. If a link event is triggered that cannot complete properly, Deephaven will prompt you to take action.

img

The Link Error dialog offers three options:

  • Ignore - Leave broken links. The error will reoccur when the user attempts to double click again.
  • Repair Links - This opens a special instance of the Link Editor that allows users to select a link to repair: img
  • Break Links - Remove the broken link(s) entirely.

Saving Linked Views

Saving linked views in your workspace is possible only if the source table and target table/plot are generated from a persistent query. Once you have established your links and set the filter parameters, click Save at the top of Deephaven console.

Linked views generated from persistent queries will remain linked and will reestablish themselves if queries are restarted for any reason or after closing and reopening Deephaven.