You should always try to build your queries within a single Explore. If you cannot find all the fields you need to build a specific report within an Explore, the AlayaCare data team can evaluate the use case and determine whether the fields you need are scheduled to be added to the Explore in the coming months or if a new Explore should be created to meet your reporting needs.
However, Looker does offer advanced functionality that allows you to incorporate data found in other Explores using the Merge Results function. For queries in two or more Explores to be successfully merged, the queries must all share a common dimension. If Looker cannot identify a shared dimension among the queries, the results will not be merged.
As an example, let’s say you want to generate a list of all your clients that shows the total number of visits scheduled for each client. If you create a query in the Visit Explore with the dimension Client Full Name and the measure Visit Distinct Count, the list will only display clients that have one or more visit scheduled. You can solve this problem by merging the results of this query with a query in the Client Explore that lists the full names of all clients. Since Client Full Name is a dimension found in both Explores, you can use this field as the common element for merging results. Once the two queries are merged, you will be able to see which clients do not have a value for Visit Distinct Count (meaning that no visits have been scheduled for them yet) from the same report.
To begin, open the Explore in which you intend to build your first query. This query will become your primary query in the merge results process.
Add the dimensions, measures, and filters you need. For the example above, you would need to open the Client Explore and add the Client Full Name dimension. In order for the results to be merged correctly, your primary query needs to be created in the Client Explore. Then select Run to retrieve the data.
Click the gear icon next to Run and select Merge Results.
Next, select the Explore in which you wish to create your second query. To continue with the example above, you would select the Visit Explore.
After selecting an Explore, choose the dimensions and measures that you wish to add to your second query. Remember that you need to choose one of the dimensions that you used in your primary query to merge the results. When you finished adding fields, click Run.
After running the second query, click Save.
Next, select which dimension you wish to use for merging the two queries together in the Merge Rules section. In the example shown below, the only dimension available to merge is Client Full Name. When you click Run, the results from this dimension in the primary and secondary queries will be merged into one complete list of client names. When merging more complex queries, you may have multiple common dimensions in the two queries that you can choose from.
Note that you can add a new query in another Explore, edit or rename your queries, or make your secondary query your primary query from here. You can delete a secondary query but not the primary query.
Once you have set your merge rules, click Run.
The merged results of the two queries will appear below. You can sort, pivot, and add visualizations for the data below.
You can then save the merged queries to a dashboard. Note that you can only download merged results as part of a dashboard. You can save merged queries as an individual Explore.
You can learn more about merging results from different Explores in Looker here.