How to Build A Query

Queries are used to combine and transform data from sources or other queries.

In this article:

Creating a Query
Query Builder
Additional Features

Creating a Query

To create a query, navigate to the queries page and select the 'New Query' button.

Enter a name for the query and press save. You will be redirected to a page similar to the one shown below.

Query Builder

Here is where you can add other sources and queries as inputs. To start off, let's add a new input.

Pressing the 'Add Input button' will display the window below.

Select all of the sources and queries you want to include and select 'Save' at the bottom. A popup will then appear asking if you would like to include the fields from the selected items. Including the fields will pull all columns into your query. Alternatively you can choose to add in the fields manually if you do not need all of the data.

For this example we will be including all of the fields.

Now that our table is populated we can take a closer look at some of its components.

Inputs:
On the leftmost column is the list of inputs that are included within the query. Clicking within the cell will redirect you to that input, allowing for quicker navigation through the app.
Clicking the icon on the right side of the cell will show additional options. These include the ability to delete, duplicate and add all fields from the input.

Headers:
At the top row of the table is where the headers are located. These are used to group together the fields for all inputs, consolidating them under one field when the query is ran.
The options menu for headers includes the ability to edit (covered in the section Editing a Column), delete, and copy the column. There is also an additional option that allows you to create a Metric based on the selected column.
Pressing the "Expand" button within the header row will show all the information for each header. The information includes the column name, data type, aggregate function, sorting method, and the format date.

Cells
All of the boxes within the table are cells. Each cell can contain either a field, SQL expression, static value, or a NULL value.

Right clicking a cell will open a menu, which allows you to copy a cell's format and apply it to any cells of your choice.

Cells have to abide by a set of restrictions:

A cell can only use fields from an input that is in the same row
Cells must be linked to a header and input
The data type of a cell must match the data type of the column

Query Builder Toolbar

At the top of the table is a toolbar that contains various features and navigational tools.

Add Column

In order to add a new column, you must use the "Add Column" dropdown.

Here you are given two options.

Blank: Adds an empty column that allows you to select a field, set a static value, create an SQL statement, or set a NULL value
Builder: Opens a popup that allows for the creation of many different types of columns. More information about the various column types can be found in the document Types of Query Columns.

Default Values

This feature allows you to set empty cells with STRING, INTEGER, or DATE value of your choice.

Note: Having a default value set, but leaving the input empty, will result in empty cells being assigned a NULL value.

Pagination

Here you are able to horizontally navigate through your table.

The leftmost and rightmost buttons will navigate to the first and last column in the table respectively.

The innermost buttons will navigate one "page" when pressed. The amount of columns on the page determines how far the table will shift.

Field List

This tool has three separate features that allow you to view and navigate to any field in the table.

View All: Shows all inputs, as well as the fields they contain. Fields that are included in the table have green checkmarks. Clicking on an included field will navigate you to that cell.
View In Use: Shows all fields that are being used by the table.
Column View: Shows all columns within the table. Selecting a column will navigate you to that column.

Editing a Column

In the header row, if you select the options icon, you will be given an Edit option. Clicking it will open a window like the one below.

From here you have the ability to:

Edit the name of the column
Change the data type that all fields must
Set how the data should be Aggregated
Set the desired format for the date (if applicable)
Choose if the data should be sorted either by ascending or descending values.

Note: The undo, redo, and reset buttons can be used if you are not satisfied with your edits. These options are located next to the "Save" and "Run" buttons.

Running a Query

Once you have finished making changes to the query, it is time to run and verify that it works.

From this bar, you can run the query by pressing the "Save & Run" button. This will save all the changes that have been made to the query and attempt to run it. If you do not wish to run the query, you can press the "Save" button. If you wish to revert any recent changes you have made, then you may either press the "Reset" button or refresh the page.
( Warning: You will not see another person's edits until you have refreshed the page after they have run the query. Saving or running the query without refreshing will overwrite their changes)

Enabling the "Run Dependents" loads all inputs before running the query, to ensure that the most recent data is being included.

After the run has completed, you should see this section has updated.

This tells you whether the load has succeeded, the last time the query was updated, the number of rows in the query, the size of the query, and the date of the most recent run.

Exporting a Query

After the query has successfully run, the data can be exported. Pressing the export button reveals two different options:

Download CSV: Directly download the query data as a CSV file. (1 GB size limit)
Create Export: Use an external connection to send the data to a specified BigQuery table within the Google Cloud Platform

You may also create an export within conx that can send the query's data on a set schedule.

Additional Features

Within the query page there is a list of tabs for a few additional features. 'Query Builder' is the main tab that is explained above.

Filters

This page gives the ability to filter out unwanted data from the query using conditional statements.

Each statement consists of a field, an operation, and a static value. Multiple conditions and groups can be linked using and/or statements. Any data that does not match the conditions will not be included when the query is run.

Date Ranges

This page allows you to select time periods to pull data from. There are two different options available for creating a date range, Calendar and Fiscal Calendar.

Calendar

With this option, you must select a date field from the query. Then a preset or custom date range must be selected. Finally, the year you would like to pull data from must be set. An optional name can also be given to the date range.

Fiscal Calendar

This option is nearly identical to Calendar. The main difference is that a Fiscal Calendar Table must exist within the query in order to create this date range. You can learn more about creating Fiscal Calendar Tables in the document Types of Query Columns.