Visual Query Builder

This page describes how to set up and configure the 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 a Visual Query Builder

To set up a Visual Query Builder component, click-and-drag the component into the workspace and configure the following:

Define the Connection which will populate the list of available data sources to build the query.
Define the Data Source which is the output for the query, and used in other components.

Refer to Visual Query Builder properties described in the next section for details on additional properties.

Visual Query Builder properties

The following sections provides details on how to configure the properties of the Visual Query Builder component.

Basics

Open the Basics properties on the right and configure the properties described in the following table.

Screenshot

Field	Description
Name	Enter a name for the component.
Data Source	This must 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 are highlighted in the query builder on the node where the error occurs. Refer to Data sources for further details.
Connection	Select a kdb database. Refer to Connections for further details.
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.
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. Note The output query requires both the KDB and Selected Nodeview state parameters

Bindings

Configure the properties described in the following table.

Screenshot

Allows node functions to be assigned to view state parameters.

Field	Description
Key	The Name of the assigned binding, this will appear in the function dropdown.
Viewstate	The viewstate to link to the function: Note 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 Set the view state Edit the function Bind the parameter to the created view state

Field

Description

Key

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

Viewstate

The viewstate to link to the function:

Note

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

Bind the parameter to the created view state

Screenshot

Style, Format, Margins

Refer to Style for common style settings.

Build a query

Users start by adding a data source to query; this can be done with a right-click on the empty Data node to open the menu of options shown below and clicking Edit Data.

Screenshot

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

	Add Filter
	Add Update
	Add Group By
	Add Join
	Add Function

Note

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 are determined by your Connection

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 includes all distinct pairing across selected columns.

Screenshot

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

Warning

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

Note

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

Example:

Copy

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