Tree Tables

A Tree Table provides a convenient way to display your data by allowing you to expand or collapse "branches" of the table as needed. When fully collapsed, the "tree" component resembles a navigation menu: clicking on the arrow in a root row opens the data for that particular branch.

Creating a Tree Table

Before you can create a Tree Table, you must first prepare your data set so that the query imposes a hierarchical order. Your data set must have an ID column that uniquely identifies each row and a Parent column that indicates the parent of each row. If the value in the parent column is null, then the row is at the top level of the hierarchy.

A Tree Table can display any hierarchical dataset; the only constraint that Deephaven requires is the unique ID and then mapping to a Parent.

In the following example, a Tree Table is created by first preparing tables that identify unique parent values that are then merged with the actual data using a SmartKey data structure (an object that allows you to use multiple values as your key) for row and parent identification.

Example 1

The following example creates a simple Tree Table branched by Sym. The original dataset being used comes from the StockTrades table in the LearnDeephaven namespace.

• We first prepare the data by creating a new table that determines and aggregates the data for our chosen parent (Sym).
• Next, we add the additional ID and Parent columns into the actual data table we want to display.
• Since the StockTrades table does not already contain a hierarchical order, it is by merging these tables that we create a data set to which we can apply the treeTable method.

syms = db.t("LearnDeephaven","StockTrades")
        .firstBy("Sym")
        .updateView("ID=Sym","Parent=(String)null","Date=(String) null", "Timestamp=(com.illumon.iris.db.tables.utils.DBDateTime) null", "SecurityType = (String) null", "Exchange=(String)null", "Last=(Double) null", "Size=(Integer)null", "Source=(String)null", "ExchangeId=(Long) null", "ExchangeTimestamp=(DBDateTime)null", "SaleCondition=(String)null")

data = db.t("LearnDeephaven","StockTrades").where()
        .updateView("ID=Long.toString(k)","Parent=Sym")

combo = merge(syms,data)

comboTree = combo.treeTable("ID","Parent")

from deephaven import *

syms = db.t("LearnDeephaven","StockTrades") \
    .firstBy("Sym") \
    .updateView("ID=Sym","Parent=(String)null","Date=(String) null", "Timestamp=(com.illumon.iris.db.tables.utils.DBDateTime) null", "SecurityType = (String) null", "Exchange=(String)null", "Last=(Double) null", "Size=(Integer)null", "Source=(String)null", "ExchangeId=(Long) null", "ExchangeTimestamp=(DBDateTime)null", "SaleCondition=(String)null")

data = db.t("LearnDeephaven","StockTrades").where() \
    .updateView("ID=Long.toString(k)","Parent=Sym")

combo = ttools.merge(syms,data)

comboTree = combo.treeTable("ID","Parent")

Let's walk through the query step by step.

• The first part of the query creates the "syms" table using data from the StockTrades table. This is the table that will create the structure for the tree components.
• The firstBy method finds each distinct value or set of values for the column listed in the argument (Sym), groups the rows containing the same distinct value, and then returns the first row for each group.
• The updateView method creates a new ID column and Parent column. The ID column specifies the parent value that children rows will have to match. Parent column values are null because this table contains the root rows of the Tree Table (the rows that open each table level). In this case, Sym does not have a parent of its own. Note: the ID and Parent columns can be named anything you'd like.
• The updateView method also sets all the remaining columns in the table as null so that they will not display any information in the resulting table. You must specify the data type for each column when casting the null value. For example, the Exchange column contains string values, while the Size column contains integers.
• The second part of the query creates the actual data table. This is the data that opens for the user when they expand the branches of the table.
• The updateView method creates an ID and Parent column; this is required for Deephaven to later map the children rows to their appropriate parent branches after the tables are merged.
• The ID must be a unique value, such as k, which refers to the index of a row in the table. ID = Integer.toString(k) ensures that the ID values across all tables are the same type, in this case Strings. Parent=Sym specifies the parent branch that the rows belong to. The third part of the query creates the combo table that merges the rows from "syms" and "data". The "combo" table contains all of the rows that the Tree Table will contain, except without the functionality of the branch structure. The ten distinct root rows appear in the beginning of the table. There are ten root rows because there are only 10 unique Syms in this table.
• The fourth part of the query actually creates the Tree Table using the treeTable method defined with the ID and Parent columns as its arguments.

To open a section of the Tree Table, click the right-facing arrow in the root row, or press Enter/Spacebar.

When a branch of the tree is open, we see the ID column includes the row number, and the Parent column includes the ID value of the root row, in this case the distinct Sym, which is AAPL.

Example 2

A Tree Table can include multiple branching levels as long as each level is assigned a unique identifier. The following query creates a similar table to the one shown above. However, in this example, the table first branches by Sym, and then again to a sublevel of Date.

syms= db.t("LearnDeephaven" , "StockTrades").where()
        .firstBy("Sym")
        .updateView("ID=Sym","Parent=(String)null","Date=(String) null", "Timestamp=(com.illumon.iris.db.tables.utils.DBDateTime) null", "SecurityType = (String) null", "Exchange=(String)null","Last=(Double) null","Size=(Integer)null","Source=(String)null","ExchangeId=(Long) null","ExchangeTimestamp=(DBDateTime)null","SaleCondition=(String)null", "USym=(String)null")

dates = db.t("LearnDeephaven","StockTrades").where()
        .firstBy("Sym","Date")
        .updateView("ID=Sym+Date", "Parent=Sym", "Timestamp=(com.illumon.iris.db.tables.utils.DBDateTime)null", "SecurityType=(String)null", "Exchange=(String)null", "Last=(Double) null", "Size=(Integer)null","Source=(String)null", "ExchangeId=(Long) null", "ExchangeTimestamp=(DBDateTime)null", "SaleCondition=(String)null","USym=(String)null")

data = db.t("LearnDeephaven","StockTrades").where()
        .updateView("ID=Long.toString(k)","Parent=Sym+Date")

combo = merge(dates,syms,data)

comboTree = combo.treeTable("ID","Parent")

from deephaven import *

syms= db.t("LearnDeephaven" , "StockTrades").where() \
    .firstBy("Sym") \
    .updateView("ID=Sym","Parent=(String)null","Date=(String) null", "Timestamp=(com.illumon.iris.db.tables.utils.DBDateTime) null", "SecurityType = (String) null", "Exchange=(String)null","Last=(Double) null","Size=(Integer)null","Source=(String)null","ExchangeId=(Long) null","ExchangeTimestamp=(DBDateTime)null","SaleCondition=(String)null", "USym=(String)null")

dates = db.t("LearnDeephaven","StockTrades").where() \
    .firstBy("Sym","Date") \
    .updateView("ID=Sym+Date", "Parent=Sym", "Timestamp=(com.illumon.iris.db.tables.utils.DBDateTime)null", "SecurityType=(String)null", "Exchange=(String)null", "Last=(Double) null", "Size=(Integer)null","Source=(String)null", "ExchangeId=(Long) null", "ExchangeTimestamp=(DBDateTime)null", "SaleCondition=(String)null","USym=(String)null")

data = db.t("LearnDeephaven","StockTrades").where() \
    .updateView("ID=Long.toString(k)","Parent=Sym+Date")

combo = ttools.merge(dates,syms,data)

comboTree = combo.treeTable("ID","Parent")

• As before, the first part of the query creates the same "syms" table.
• The second part of this query creates another parent table, "dates", that also uses the firstBy method to find each distinct set of values for the columns listed in the argument. In this case, the arguments are Sym and Date.
• The "dates" table sets Parent to the ID value of its parent, Sym, because this will be a sub-level of the root rows in the syms table. However, as a sublevel, ID will be set to the Parent value joined with its own unique identifier, Date. Then, the other columns are set to null so that no other data will be displayed.
• Now we create our table of data. The Parent column is set to the ID value of Sym+Date, but the ID column will still be a row number as a string.
• Finally, we combine all three of the tables together in the "combo" table, and use the treeTable method with our two arguments, ID and Parent, to create "comboTree".

When we click on the root row GOOG, we now see rows for each individual Date in the table; expanding the row for August 22, 2017 shows the data associated with Google on that date.

The ID column shows the unique identifier for each level: Sym for the root level, Sym+Date for the first sublevel, and the row number for the second sublevel. The Parent column shows the level to which each child level applies.

Working with Tree Tables

Filtering and sorting Tree Tables works in the same fashion as any other table.

Remember that the sections are not actually distinct partitions, so any sort applied to one section affects the whole table. Using our previous example, if the tree for AAPL is expanded and we then apply a sort on the Exchange column, the rest of the table branches in the table will also be sorted on the Exchange column. If the table is filtered to one particular Sym, only its root row will be available in that table. AutoFilters may be inherited from the parent table, or applied directly to the Tree Table.