Exclusive query methods

AutoFilters

An AutoFilter allows users to filter a Deephaven table based on an automatically generated list of unique values found in the column. This feature is enabled for columns through the query used to create the table by applying the .layoutHints method with an autoFilter argument. The syntax follows:

.layoutHints(LayoutHintBuilder.get().autoFilter("ColumnA","ColumnB","ColumnC"))

import com.illumon.iris.db.tables.utils.LayoutHintBuilder
t1=db.t("LearnDeephaven", "StockTrades").where("Date=`2017-08-21`")
        .layoutHints(LayoutHintBuilder.get().autoFilter("USym","Exchange","Size"))

from deephaven import *

t1=db.t("LearnDeephaven", "StockTrades").where("Date=`2017-08-21`")\
    .layoutHints(LayoutHintBuilder.get().autoFilter("USym", "Exchange", "Size"))

This query generates the t1 table using data from the StockTrades table in the LearnDeephaven namespace, filters the data to show only data from August 21, 2017, applies the layoutHints method, and then creates an AutoFilter for the USym, Exchange, and Size columns.

Once the layout hints have been applied, a filter icon will appear when the cursor is hovered over the column heading, as shown in the screenshot below:

Clicking on the empty funnel icon will change the icon to a filled funnel and will open a pop-up menu that contains a searchable list of the unique items in that column.

By default, the dialog will return up to 1,000 unique values from the column. In this case, there are only nine unique values in the Exchange column to display.

If the column contained more than 1,000 unique values (such as Size), a More Data button would appear. When selected, the menu could then return up to 1 million unique items.

The Search field at the top may also be used to filter the list, which is particularly useful when you have a large number of items.

To filter the table, select the items from the pop-up menu and click OK. The table will then be filtered to show only those rows that contain the selected items in that column. The header of the column turns blue to indicate a filter has been applied.

To clear any AutoFilter(s), right-click in the column header and select Clear Filter or Clear All Filters from the drop-down menu.

Optional Properties

As stated above, the default initial fetch size for the AutoFilter dialog is 1,000 unique items. This can be changed globally by modifying the property:

AutoFilterSelectionPanel.defaultInitialFetchSize=[size]

You can also set the maximum fetch size by modifying the property:

AutoFilterSelectionPanel.maxAutoFilterItems=[size]

The fetch size can be set per-column by including this method in your layout builder:

.autoFilterFetchSize("ColName", int size)

Additionally, modifying the maximum cache size property will keep selected values in the cache so that they always appear in the AutoFilter dialog regardless of the fetch size.

AutoFilterSelectionPanel.maxCacheSize=[size]

This property defaults to 100. Cached items appear first (even after they become unselected).

These should be configured in the iris_console and interactive_console stanza.

Aggregating Grand Totals

Users can configure aggregated column values in a table, such as the total sum of values in a column or an average value of a particular column, using the setTotalsTable method in a persistent query. Alternatively. data aggregation can be accomplished through the Deephaven console using the Show Totals option in the right-click context menu. This menu shortcut can also be used to edit total previously configured via query.

The following example creates a table using data from the StockQuotes table in the LearnDeephaven namespace. The final generated table will include the Grand Totals section at the top of the table:

import com.illumon.iris.db.v2.TotalsTableBuilder
t1 = db.t("LearnDeephaven" , "StockQuotes").where("Date=`2017-08-25`")
t2 = t1.setTotalsTable(new TotalsTableBuilder()
        .setOperation("Bid", "Avg")
        .setOperation("Ask", "Avg"))

from deephaven import *

t1 = db.t("LearnDeephaven", "StockQuotes").where("Date=`2017-08-25`")
t2 = t1.setTotalsTable(TotalsTableBuilder()
    .setOperation("Bid", "Avg")
    .setOperation("Ask", "Avg"))

This Grand Totals section (and Totals section if present) will display the average size of the Bid column and the average size of the Ask column.

• The query used for the example did not configure the setShowGrandTotalsByDefault argument to true (see the list of available methods below), so the Grand Totals section will appear in its collapsed state when the table is shown.
• The Totals section will only appear if the table is manually filtered.
• The parameters set in the query become the Table Default. However, they do not prevent individual users from customizing the aggregations in their workspace using the Edit Totals dialog.

Available methods

The following methods are included within TotalsTableBuilder:

Available aggregation operations

The following aggregation operations may be used:

Available formatting options

Only Date and Decimal are supported as arguments within the in TotalsTableBuilder methods. The expressions are the same those used to format columns in a table.