What are the query builder actions?

When using the Visual Query Builder you can use any of the following actions in your query.

To illustrate their use, imagine the following sample tables:

Data for examples

Expenses table

Category table

Actions

Join data

Join data to combine the contents of several assets. To join data you'll need to select:

• The type of join (see table below).
• The left asset and the column data to match with the right asset's column.
• The right asset and the column data to match with the left asset's column.

For example, to get a set of results that shows the category of expenses along with the spend:

• Select Inner Join or Left Join as the join type
• Select Expenses for the left table, and CATEGORY_ID as its column
• Select Category for the right table, and ID as its column

The resulting table would be:

Filter

Filter data to return only some of the rows. To filter data you'll need to select:

• The column data by which to narrow the rows.
• The operation used to compare that column's data.
• The value to compare against.

For example, to retrieve only the advertising spend:

• Select NAME for the column
• Select Equal to for the operation
• Enter Advertisement for the value

The resulting table would be:

Group

Group data to combine rows together into buckets. To group data you'll need to select the column by which to bucket the data.

For example, to bucket the data by category:

• Select NAME

If you remove the filter step above, the resulting table would be:

Aggregate

Aggregate data to calculate a metric from many rows of data. For example, to calculate a metric against each of the buckets created by grouping. To aggregate data you'll need to select:

• The column on which to run the calculation
• The calculation to run:
  • Count: calculate the total number of rows
  • Unique Count: calculate the number of unique values for this column across the rows
  • Sum: add all the values of the rows together
  • Max: find the highest value in the rows
  • Min: find the lowest value in the rows
  • Average: add all the values of the rows together, then divide by the number of rows

For example, to calculate the total spending by category:

• Select SPEND for the column
• Select Sum for the calculation

The resulting table would be:

Sort

Sort data to return the rows in a defined order. To sort data you'll need to select:

• The column by which to order the results
• The direction in which to order them:
  • ASC: return the lowest value as the first row and highest value last
  • DESC: return the highest value as the first row and lowest value last

For example, to return the category with the highest spending:

• Select sum_SPEND for the column
• Select DESC for the order

The resulting table would be: