Subquery search
When you want to do a search on top of another search, commonly known as a subquery, you can use the IN keyword.
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 requires 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
- in
-
Query in query search (intersection of two sets). Must match last attribute before keyword with first attribute inside subsearch. Doesn’t support use of the
vskeyword.- 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. Doesn’t support use of the
vskeyword.- 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 query, and paste it into your new search as the
inclause.
Related information
