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.
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:
-
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.
-
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 first attribute inside subsearch.
- 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
- 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
-
Start a new search.
-
Run the search you plan to use for the
inclause. That is, the first level search.Check that the result is what you’re looking for.
-
Copy that search, and paste it into your new search as the
inclause.
