Visual SQL - Data Pipeline Steps

Beta

Data Pipeline Steps allow you to perform a variety of post-query manipulations in Chartio. Steps can be added to the pipeline under any dataset to edit the results of that specific dataset’s query.

Example list of Data Pipeline steps

Pipeline Steps
Columns Rows Transform
Calculated Column Sort Rows Pivot Data
Rename Columns Limit RowsZero Fill Data
Hide Columns Filter Rows
Reorder Columns

Calculated Column

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

Add a Calculated Column to your query results. Use a custom formula to manipulate or add data. You can use the available auto-complete formulas listed below.

Add Calculated Columns to your dataset

Available formulas

  • pow(base, exponent)
  • rowid
  • dateadd(values, amount, unit)
  • datediff(values, values, unit)
  • datepart(values, unit)
  • datesub(values, amount, unit)
  • percent_change(num_values)
  • running_total(values)
  • format(values, format_string)
  • moving_avg(values, sample_size)

SQLite functions can also be used in any Calculated Column. Below are quick links to SQLite’s documentation outlining the various types of functions:

Filter Rows

Add a Filter step to your Data 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.

Example of creating a Filter step

Regular Expression Matching

Any text column supports filtering by Java-style regular expressions in the Filter Rows Pipeline step. 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 Rows Pipeline step, see our Filter Pipeline Step Example Scenarios article.

Hide Columns

Hide a column in your query results by clicking Hide column in its Column Actions menu. If you need to use hidden columns in a Formula, hide the columns as the last step.

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

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

Pivot Data

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 Data Example

Before Pivot:

Table before the Pivot step

Pivot step:

Pivot step example

After Pivot:

Table after the Pivot step

Rename Columns

Rename a column by clicking Rename column from its Column Actions menu. This is useful for display purposes or shortening column names for ease of use in formulas.

Rename columns in the pipeline

Reorder Columns

Move a column to make it first or last in the table using the Move to Start or Move to End buttons from its Column Actions menu. Use combinations of these buttons to get the desired column order in your table.

Note: It’s generally best to reorder columns in one of the last steps, as any editing that involves column changes will affect your reordering settings.

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

Sort Rows

Apply an ascending or descending sort to a column’s results from its Column Actions menu.

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

Zero Fill Data

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 will fill in all date and numeric columns in your query results.

Zero Fill Example

Before Zero Fill:

Table before the Zero Fill step in the Data Pipeline

After Zero Fill:

Table after Zero Fill step in the Data Pipeline


Related Beta Help Articles

See more