Drill down on a table

This walkthrough tutorial will help you drill down on a table and present the results in a chart. The chart will update based on which row is clicked on the table.

Set up query nodes for chart

1. Create query node(s) to use for a table and chart. These are likely to be different nodes, which will allow you filter data for a chart without changing the table.

2. On the data node for the chart, write a query with a SQL parameter you'd like to filter by. See Query for more information on parameter syntax and examples. This example in a transformation node will filter based on a ticker value.

   

Query:

SELECT date, ticker, name, close as stock_price
FROM company_daily_stock_price
WHERE ticker = '{{ticker}}'
ORDER BY date

Parameter Initialization(Add a parameter named ticker with default value of ORCL):

{
  "ticker": "ORCL"
}

Create your table

Create the table that the drill down will be connected to. See Create a table. For the table, create another query node for table data. Use this query node as the Data source of the table.

Query:

WITH RankedObservations AS (
    SELECT 
        *,
        ROW_NUMBER() OVER (PARTITION BY ticker ORDER BY date DESC) AS row_number
    FROM company_daily_stock_price
)

SELECT ticker, name, close as stock_price, volume, website, industry, subindustry
FROM RankedObservations
WHERE row_number = 1

Create your chart

On the same page, drag a Chart element. As the chart's data source, select the query node with the SQL parameters. Then configure the chart as desired. For example, set date for x dimension and stock_price for y metric.

Create the interaction

To perform a drill down, a click event on the table is used to trigger the change of query node's SQL parameter so only the data for the selected ticker is shown on the chart. For example, if we're filtering by ticker, we'll pass in ${event.rowData.ticker} as the action parameter.

The chart will now show data for only that ticker after click on a certain row.

You can also create additional tables, or other elements, that will present your drilled-down data. For example, add a text to display the company's name. Add one more Action - Element - Set text after Row Click, then set the text to ${event.rowData.name}

Preview or publish your app to see the result.