Query sets

You can create sets, which can be used to classify values in a list that you can then reuse across multiple analyses. Answer, formulas and parameters are generally available only when in use in an Answer, and must be remade if you navigate away from the Answer without saving. Sets are reusable across many answers with a single point of definition. A set is built from a Worksheet however it does not belong to the Worksheet. When a set is initially created it is only available to the creator. When a set is used in an Answer or Liveboard, and this Answer or Liveboard is shared with specific users or groups of users, the set is then available for use as a community object.

ThoughtSpot now supports query sets.

Query sets are in early access and off by default. To enable this feature, contact your ThoughtSpot administrator.

To create a query set, first define the business question you wish to answer.

For example, you may want to create a set to show the first sale date per customer.

To create a column showcasing this defined list of values, follow these steps:

  1. Navigate to the Search Data page by clicking the Search Data button.

  2. Click Add and select Query set.

    Add query set

  3. Select a column from the left side bar, or type a column name into the search bar.

    Query set coolumn

  4. Click Next.

  5. In the Select name and description window, complete the following fields:

    1. Set name: Name your query set something meaningful.

    2. Set description: Describe what this query set represents.

      Query set name

  6. Click Next.

  7. Under Select a base column, select a Base column. Select the column you are using as the base of your list of values.

    It is important that for each base column value there is only a single return column value. If this is not true, then the query generation will result in a many-to-many join. This may be desired. This should be reviewed as this will likely lead to incorrect values as result of overcounting.

  8. Select either Conditions, Bins or Column under Define groups for included query values.

    Conditions allow you to create groups using operators.

    Bins allow you to create groups based on bins of specified sizes for aggregate results.

    Column allows you to select a column with output groups. Each row in the base column will be assigned to an output group based on its value from this column.

  9. Enter your desired values for the conditions, bins or column.

    1. For conditions, you can choose to add multiple conditions by clicking Add condition.

      Multiple conditions can be combined with so that all conditions should be true or alternatively only a single condition as true.

    2. For conditions, you can choose to add multiple groups by clicking Add group.

      If multiple group conditions return true for an anchor column value then the order of the groups takes precedence. That is for each value only one condition will return true.

    3. For conditions, you can choose to show remaining values as either Individual values or as a Group. If you choose to group the remaining values, you can enter a name for the group.

      For example, you could create a query set that list each customer, their sales dollar amount and then add a formula that ranks the amount in descending order. You could then add groups to define Gold, Silver, Bronze levels where Gold is Ranked 1-10, Silver 11-50 and Bronze everyone else.

      Query sets esample

  10. Under Define groups for excluded query values, select either Hide excluded values or Group excluded values. If you group excluded values, you can give the group a custom name.

  11. Click Save.

Passthrough filters for sets

You can now pass filters from a query into a set definition without needing to remake the set to include the filter.

For example, if you create a query set defined as a list of SKUs that are ranked based on sales, where 1 through 10 are defined as Gold, 11 through 50 are defined as Silver, and the remainder are defined as Bronze, you can now add a filter such as Region.

You can configure the set’s filter behavior from the TML, instructing ThoughtSpot to exclude or include specific columns, or to accept all filters by default.

To edit a set’s behavior regarding filters, follow these steps:

  1. Open the Answer containing the set and scroll through the list of sets in the side menu until you find the name of the set.

  2. Click the more options icon next to the set name and select Edit TML.

  3. Scroll to the bottom of the TML file until you reach the pass_thru_filter property. Set accept_all to true to allow filters to pass through to the set. Set the property to false to keep the default behavior.

  4. Click Publish in the top right corner. Confirm your selection by clicking Publish in the pop-up window.

  5. Click Close and close the TML window.

Limitations

  • We do not currently support row-level security for query sets. Adding row-level security to a query set results in a query generation error.

  • It is not possible to un-share a set.

  • Sets are only shared with View permissions.

Was this page helpful?
×