Subquery search
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
| Keyword | Description | Example |
|---|---|---|
attribute in (attribute subsearch) |
Query in query search (intersection of two sets). You must match the last attribute before the keyword with the first attribute inside the subsearch. |
|
attribute not in (attribute subsearch) |
Relative complement of two sets. You must match the last attribute before the keyword with the first attribute inside the subsearch. |
|
Create an IN search
-
Start a new search.
-
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.
-
Copy that search, and paste it into your new search as the
inclause.
