Skip to content

Visual Query Builder

A graphical tool enabling users to quickly build and visualize database queries without requiring knowledge of kdb or q.

Visual Query Builder is best used in conjunction with another component, such as Data grid. To see this in action, check out the Demo Data dashboard and switch to the Visual Query Builder tab.

Screenshot

Set up

  1. Define the Connection which will populate the list of available data sources to build the query.

  2. Define the Data Source which is the output for the query, and used in other components.

Basics

Screenshot

Data Source
This should match the data source used as the output (where Selected Node and KDB are assigned view state parameters). Any errors returned by the data source will be highlighted in the query builder on the node where the error occurs.
Data sources
Connection
Select a kdb database.
Connections
Direction
Define order flow of visual query: left-to-right, right-to-left, top-to-bottom, bottom-to-top.
Dialog Placement
Configuration dialogs can appear to the left or right of the query builder interface, or as a popup
Auto Save Dialogs
Automatically save value changes made in left or right dialog menus, but not for popup dialogs.
Read only
Disables dialogs, preventing changes from being made to the query.
Force Selection
Prevents Visual Query Builder from a no-node-selection state. Selecting a node runs the query to that point - ignoring nodes beyond it. If no nodes are selected, then all node are factored; when Force Selection is enabled, a single node must be selected, which is the last node if no other node is selected (default).
Selected Node
A view state parameter assigned to the selected node within the visual query.
Source

A view state parameter storing the complete structure of the query as JSON. It is best used in conjunction with the Editable list component to toggle between several different queries. See the Demo Data dashboard for an example of this.

Screenshot

KDB

A view state parameter storing the output kdb string. This is used in the output data source, which is generally rendered inside a data grid.

Screenshot

Also assign Selected Node

The output query requires both the KDB and Selected Node view state parameters

Screenshot

Bindings

Screenshot

Allows node functions to be assigned to view state parameters.

Key
The Name of the assigned binding, this will appear in the function dropdown.
Viewstate

The viewstate to link to the function:

Check View State Type

The view state type must be of the same type as the parameter you plan to assign it to, or else it won't be available to select in the function parameter dropdown.

Create the binding

Screenshot

Set the view state

Screenshot

Edit the function

Screenshot

Screenshot

Bind the parameter to the created view state

Screenshot

Build query

Users start by adding a data source to query; this can be done with a right-click on the empty Data node.

Screenshot

Once a data source is assigned, right clicking inside the query builder will offer additional options:

Screenshot

Streaming queries

Support for streaming queries is available with Builder of the data editor.

Add data source

Select a data source from the drop down. Available data sources will be determined by your Connection

Screenshot

Screenshot

Add filter

Filter against the data source using equal, not equal, less, less or equal, greater, greater or equal, is blank, is not blank, in, not in or use a histogram range selector.

Check distinct to pull distinct values from all checked columns; each additional distinct column will include all distinct pairing across selected columns.

Screenshot

Screenshot

Select columns from the data source. Data Columns can be renamed in the Select - Columns option.

Shared column names

When creating a Join between different data sources, the joining column(s) must share the same name.

Select columns, and filter order can be re-organized using drag-and-drop.

Add update

Screenshot

Update values within a column

Add group-by

Screenshot

Create aggregate groupings for data columns; options include: avg, cor, count, cov, dev, first, last, max, med, min, prd, scov, sdev, sum, svar, var, wavg and wsum

Column grouping order can be re-organized using drag-and-drop

Add join

Screenshot

Join an additional data source or function to an existing data source in your visual query. Options include left, inner, union, asof, upsert and plus.

Screenshot

Add function

Screenshot

Add a pre-configured Function into the visual query

Dashboards SDK

Visual Query Functions for Dashboards SDK are added to the demo.q file in the Sample directory.

Example:


api.mortgageRepaymentCalc:{[res;years]
  months: years * 12;
  // rate: (interestRate % 100);
  update Monthly_repayment: ceiling((Price * 1.06) % months) from res(0);

where years is an adjustable view state parameter in the dashboard

Style, Format, Margins

Style for common style settings