Documentation Visual SQL (beta)

Visual SQL Actions

Visual SQL Actions allow you to perform a variety of post-query manipulations in Chartio. Actions can be added to the Pipeline under the initial Query to edit that query’s results.

There are two sets of Actions:

  • Table Actions are the set of Actions that affect the entire Result Table and are found directly above the Result Table
  • Column Actions are the set of Actions that affect a single column, most of which are found in each column’s Column Actions menu – the dropdown to the right of the column name in the Result Table

    Note: The Column Actions menu only appears after you click Run Query for your initial Query.

Locations of Table and Column Actions

Keyboard shortcuts! For all Actions (except Add Query and Filter), you can submit their forms by pressing Enter or Return.

Visual SQL Table Actions
Add Query Join Formula Column Reorder Columns
Sort Rows Pivot Group & Aggregate Zero Fill
Filter Rows Limit Rows Unpivot Transpose
Visual SQL Column Actions
Apply Formula If...then Rename Column Hide Column
Sort Rows Move to Start/End

Add Query

Add a new Query by clicking Add Query above the Result Table

Before you can show your data in a chart, you need to get some data to show. This is why every chart must start with an initial Query. To help you remember this requirement, new charts have a Query Action added by default.

The Query is a major part of Visual SQL, so we gave it a dedicated page. Check out how to Start a Query to learn more about the Query Action.

Once you’ve executed your initial Query, you can add a new Query by clicking Add Query above the Result Table. When you click “Add Query”, a dropdown appears, where you can choose to either…

  • create an empty New Query or
  • copy an existing Query.

Create a new Query or copy an existing Query

Rename a Query

To better identify the purpose of the Query, you can rename it by clicking Query N at the top-right corner of the Query, typing in the desired Query name, then pressing Enter or clicking outside of the text field to save the new Query name.

Collapse a Query

To collapse the Query, click the Collapse icon at the bottom-right corner of the Query.

Join

Merge the result sets of two Queries by joining them together. You can’t manually add a Join Action to the Pipeline; it only appears in the Pipeline after you’ve merged a second Query (See Merging Queries to learn more). You can choose a different join type and–depending on the chosen join type–choose how many columns to join by and whether to include or exclude certain types of data from the merged result set.

  • You can select the number of columns to join by for Outer, Inner, and Left Joins.
  • For Outer Joins, you can choose whether to include or exclude NULL match rows.
  • For Unions, you can choose whether to include or exclude duplicate rows by choosing either all or distinct values. If you choose to include or exclude query names, a column titled Layer will be added to your result set; it labels each row with the Query that it came from.

Edit the join type of your merge

Apply Formula

Edit an existing column by clicking Apply formula in its Column Actions menu. You can use available Guided Formulas or a Custom formula to change the values in the column itself, rather than using a Formula Column Action then hiding the original column.

The available Guided Formulas depends on the column’s data type, but all columns allow you to use a Custom formula.

Filter

Add a Filter Action to your Pipeline Filter rows on certain conditions. Choose to include or exclude rows matching any or all specified filter conditions.

  • Matching all conditions
    Uses AND logic. Row will be included or excluded only if it matches every condition.
  • Matching any conditions
    Uses OR logic. Row will be included or excluded if it matches one or more conditions.

Unlike the filters in a Query Action, you can only filter by columns in your result set.

Multiple Filter Actions in the Pipeline are connected by an implicit AND operator. With this, you can create a chain of AND-OR filter conditions.

If you need more help, check out our OR filter examples or our explanations on the different filter operators.

Example of creating a Filter Action

Regular expression matching

Any text column supports filtering by Java-style regular expressions in the Filter Action. Matching is case-sensitive and unicode-aware by default. For case-insensitive matching, add (?i) to the beginning of your regular expression.

For examples of ways to use the Filter Action in the Pipeline, check out our FAQ: Filter Action examples.

Formula Column

Add a Formula Column by clicking the Formula Column button above the Results Table

Add a Formula Column to your query results by either:

  • clicking Formula Column above the Result Table
  • or clicking the rightmost column labeled +Formula Column in the Result Table.

Use one of our Guided Formulas or a Custom Formula to manipulate or add data.

Group & Aggregate

Add a Group & Aggregate Action by clicking the Group & Aggregate button above the Result Table

Change the grouping or aggregation of columns. This is exactly like choosing to group or aggregate a column from its Aggregation menu in a Query Action, but now you can change the group or aggregation type anywhere in your Pipeline.

The aggregation types also include an extra option: Concatenate. This strings together grouped values into a comma-separated list.

Example of Concatenate and Count of Distinct in a Group & Aggregate Action

Hide Column

Hide a column in your result set by clicking Hide column in its Column Actions menu. Once you hide a column, it won’t show in your Result Table. If you need to use hidden columns in a Formula, hide the columns as the last step. Columns must be present in the Result Table to use in other Actions, aggregations, or custom formulas.

Click Hide Column from a column's Column Actions menu to hide it in your Results Table

If…then

Click If…then from a column’s Action’s menu to add a column to your result set where the values depend on whether or not any of the specified conditional statements are met.

Note: If you want to add multiple conditions for a particular case, you’ll need to use a Formula Column, select Custom as the formula type, then type your custom CASE statement.

Limit Rows

Limit Rows button Limit the number of rows included in your query results. This can be useful when applied after a sort. For example, sort sales in descending order and limit rows to 10 to show the top 10 sales. Offset allows you to select a specific range of rows. For example, to include only rows 10-30, set the limit to 20 and the offset to 10.

Limit Rows step example

If you’re trying to limit the rows of your initial Query, you could modify the row limits in the Query Action itself rather than adding a separate Limit Rows Action.

Pivot

Pivot button If your chart has one numeric column grouped by two categorical columns, such as count of activity grouped by month and by activity type, you’ll want to pivot the data so you can use it in a chart.

Pivot tables require 3 columns. Your data is pivoted on the second column—i.e., the second column becomes the column headers.

  • Click Pivot above the Result Table.
  • Select Unsorted, Ascending, or Descending for the column sort direction to customize the column order in your pivot table.
  • Choose the aggregation for duplicate group values; the available aggregations are SUM, AVG, MIN, MAX, GROUP_CONCAT, and Auto. Auto will choose either SUM or GROUP_CONCAT, depending on the data type.
  • Click Save to apply the pivot.

Pivot example

Before Pivot:

Table before the Pivot step

Pivot step:

Pivot step example

After Pivot:

Table after the Pivot step

Rename Column

Rename a column by doing either of the following:

  • Click its column name in the Result Table. Type in the new column name then press Enter or click outside of the text field to save it.

  • Click Rename column from its Column Actions menu.

    Rename a column from its Column Actions menu

This is useful for display purposes or shortening column names for ease of use in formulas. Once you rename a column, you’ll use the new name in all future Actions.

Note: Renaming a column while editing a Query will update the column alias in the query itself, not add a Rename Action to the Pipeline.

Reorder Column

Reorder columns in your result set. There are a couple different ways to reorder columns:

  • Reorder Columns (Table Action) allows you to quickly drag and drop columns in the Result Table to put them in the desired order.

    Click Reorder Columns to drag and drop columns in the Result Table

  • Move to Start or Move to End (Column Actions) makes the column the first or last column in the table, respectively.

    Click Move to Start or Move to End from a column's Column Actions menu to reorder it in your Results Table

Sort Rows

  • Sort Rows (Table Action) allows you to sort multiple columns in a single step, just like how the Query sort works.

  • Sort Rows (Column Actions) applies an ascending or descending sort to a column’s results from its Column Actions menu. Keep in mind that sorting isn’t nested like it is in a Query. If you add multiple Sort Actions, the last one listed in the Pipeline takes precedence. All the other Sort Actions before it will basically be ignored.

    Sort a column's row in ascending or descending order from the column's Column Actions menu

Transpose

Transpose button

Switch your rows and columns.

Note: There are no limits on the input table format, but the resulting table cannot contain more than 1000 columns.

Unpivot

Unpivot button

Unpivots your table, turning your column headers into a single column—basically the opposite of what the Pivot Action does.

Select Three column result to ignore the first column and only unpivot the second to the last columns of your result set

Unpivoting your data is useful when you have multiple single-value columns that you want to use in a visualization, such as a Pie, Line, or Bar chart.

Unpivot example

Before Unpivot:

Before unpivot

After Unpivot:

Example for three-column result:

Zero Fill

Zero Fill button Zero Fill fills in missing values in your data with zeros. It’s important when charting a time series with missing dates, or when performing calculations on columns that contain NULL values.

Note: Zero Fill fills in all date and numeric columns in your query results.

Zero Fill example

Before Zero Fill:

Table before the Zero Fill in the Pipeline

After Zero Fill:

Table after Zero Fill step in the Pipeline