What are the query builder actions?

Have more questions? Submit a request

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_ID MEDIUM SPEND
1 Facebook 447000
1 Google 94500
1 LinkedIn 12300
2 Sales 700000
2 Marketing 400000
3 Salesforce 250000
3 Hubspot 75000

Category table

ID NAME
1 Advertisement
2 Salary
3 Software
4 Other

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.
Type of join Explanation
Inner Join returns only matching records from both the left table and the right table.
Left Join returns all records from the left table, and only matching records from the right table.
Right Join returns all records from the right table, and only matching records from the left table.
Outer Join returns all records from both tables.

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:

CATEGORY_ID MEDIUM SPEND ID NAME
1 Facebook 447000 1 Advertisement
1 Google 94500 1 Advertisement
1 LinkedIn 12300 1 Advertisement
2 Sales 700000 2 Salary
2 Marketing 400000 2 Salary
3 Salesforce 250000 3 Software
3 Hubspot 75000 3 Software
πŸ’ͺ Did you know? You can join more than 2 assets by clicking the Add another link at the bottom of the Join data tile. Each additional join will add objects to the list of assets that can be used on the left of the join.

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:

CATEGORY_ID MEDIUM SPEND ID NAME
1 Facebook 447000 1 Advertisement
1 Google 94500 1 Advertisement
1 LinkedIn 12300 1 Advertisement

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:

NAME
Advertisement
Salary
Software
πŸ’ͺ Did you know? It may look like you've over-simplified the data now. If your query stopped here, that would be true. But think of this action as preparation for other actions, like aggregating.

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:

NAME sum_SPEND
Advertisement 862800
Salary 1100000
Software 325000

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:

NAME sum_SPEND
Salary 1100000
Advertisement 862800
Software 325000

Related articles

Was this article helpful?
1 out of 1 found this helpful