Subquery search (in keyword)

You can use the IN keyword to complete a nested search

Beginning with version 5.2 of ThoughtSpot, you no longer need a View when you want to do a search on top of another search, commonly known as a subquery. You can use the IN keyword instead.

in search bar example

Suppose you must narrow a result set based on criteria defined using a search. For example:

Find the top 5 stores by sales in the bakery department

Then you want to see all sales across all departments for those five stores.

In past versions, you’d have two options:

  1. Do your first search to find top 5 stores by bakery sales and save it as a View. Then join the view to your worksheet and search both together to get your answer.

    This approach gives a dynamic result that’s computed on the fly each time, but it require the Can Manage Data privilege and requires multiple steps.

  2. Save the names of the five stores with the most bakery sales and use them as a filter in a new search.

    This approach is easy to accomplish for ad hoc searches, but doesn’t compute dynamically.

Now you can do this analysis in a single search using the "in" keyword. This provides the benefit of computing the answer dynamically without the extra steps required when using a view.

The "in" keyword

Starting with release 6.2, searches with the in keyword do not include {null} values. To include these {null} values, create a formula for the relevant attribute in your search, to convert {null} values to 'unknown,' or some similar word.
in

Query in query search (intersection of two sets). Must match last attribute before keyword with the first attribute inside subsearch. The first attribute in the sub-string must match the attribute prior to the in keyword.

Syntax
attribute in (attribute subsearch)
Examples
store name in (top 10 store name by sales footwear)

product name 2014 product name in (product name 2013) sales

revenue by store name in (top 10 store name by sales footwear)
not in

Relative complement of two sets. Must match last attribute before keyword with first attribute inside subsearch.

Syntax
attribute not in (attribute subsearch)
Example
product name 2014 product name not in (product name 2013) sales

Find sales for all products ordered in 2014 that were not ordered in 2013

Create an IN search

  1. Start a new search.

  2. Run the search you plan to use for the in clause. That is, the first level search.

    Check that the result is what you’re looking for.

    in search first step 2020

  3. Copy that search, and paste it into your new search as the in clause.

    in search example 2020
Was this page helpful?
×