# Queries

Insights enables you to perform queries on your data using a no-code interface.

Query builderBuild a query


# How it works

The query builder occupies the left part of the page. Similar to the recipe editor, you can build queries in a series of sequential steps (operations).

  • Each step operates on the columns derived from the previous steps. Initially, the available columns is defined by the data source you select. Some queries, including summarize and join, can change the column set.

  • You can build queries with steps in any order. Your queries can contain any amount of steps.

  • Workato also generates a data preview (results table) for your query, occupying the bottom right of the page. Before you visualize your data, make sure this preview appears as expected.


# Available queries

Insights supports the following queries:

Available queriesAvailable queries


# Filter

Extract the information you need from your dataset by specifying criteria the data must satisfy.

To filter your data, specify the following:

  • Data column

  • Select the data column you plan to filter.

  • Condition

  • Specify the criteria the data you've chosen must satisfy. Available conditions depend on the type of data you select. You can chain conditions using AND.

  • Value

  • Certain conditions require you to specify the Value. For example, if you apply a filter to a Short text column and apply an Equals condition, you must supply the Value your data must equal.

The following conditions are available, based on data type:

Text

Available operands:

  • Equals
  • Contains
  • Starts with
  • Ends with
  • Is not equal to
  • Is null
  • Is not null
Integer

Available operands:

  • Equals
  • Is not equal to
  • Is less than
  • Is greater than
  • Is less or equals
  • Is greater or equals
  • Is null
  • Is not null
Decimal

Available operands:

  • Equals
  • Is not equal to
  • Is less than
  • Is greater than
  • Is less or equals
  • Is greater or equals
  • Is null
  • Is not null
Boolean

Available operands:

  • Is true
  • Is false
  • Is null
  • Is not null
Date

Available operands:

  • Is
  • Within
  • Is on or before
  • Is on or after
  • Is null
  • Is not null
DateTime

Available operands:

  • Is
  • Within
  • Is on or before
  • Is on or after
  • Is null
  • Is not null

# Summarize

Present aggregated or summarized information from your dataset.

  • Metrics
  • You can summarize your data using the following available metrics:
      • Count of rows
      • Sum of
      • Average of
      • Max of
      • Min of
      • Cumulative count of rows

Cumulative functions are usually used with grouping by a Date or Date Time column and enable you to display the total progress of a certain metric over time.

  • Data column

  • Select the data column on which you plan to perform the function.

  • by Data column

  • In the By data column block, determine how to organize (group) the data. In the following example, we used a summarize query to return the sum of dollars saved, organized by process.

Summarize querySummarize query

  • by Rule
  • This function is only available for date and datetime data types. Available rules include:
      • By week
      • By month
      • By day

# Join to

Combine data from different sources into a single result set. This is particularly useful when you want to retrieve information that is associated with each other, such as getting details about orders along with customer information.

You can also join data sources of different types. For example, you can use the Customer ID custom column in a job history report to bring in information from a customer data table.

  • Joined to

  • Select the data you plan to combine with your data. You can choose from any of the data sources Insights supports.

  • Current result

  • Select a data column from your original data source that you plan to join with your secondary data source.

  • {Data source}

  • The name of this field is dynamic and depends on the data source you select from the Joined to picklist. Select a data column from your secondary data source that you plan to join to your original data source.


# Sort by

Sort your data in ascending or descending order.

  • Data column

  • Select a column from the picklist.

  • Order

  • Sort the data in Ascending or Descending order.


# Row limit to

Limit the number of rows of data Workato displays.

  • Limit row number
  • Determine the number of entries to display.


Last updated: 7/18/2024, 7:11:10 PM