Merging Results from Different Explores

On this Page
Docs Menu

Explores in Looker are designed by your Looker developer to combine the data from your database tables in the best way, using defined relationships between data fields and tables. Because of this, it is best to use a single Explore to examine your data.

However, there may be times when your developers haven’t created the relationships you need, or they faced technical limitations. In these cases, Looker lets you combine data from different Explores to create data tables and visualizations.

Using the Merged Results feature, you can create a query from an Explore, then add queries from other Explores to display the merged results in a single table. From there, you can examine the data, pivot fields, and create visualizations.

Starting in Looker 5.0, Merged Results is an experimental Labs feature that must be set up by your Looker admin as described in this article. Admins can opt into the experimental beta group by creating and adding members to a Merged Results Beta Users group. Then the experimental feature appears and can be enabled.

Understanding Merged Results

When you merge queries, you start out by creating a single query from a single Explore, and then you add other queries onto that first query.

By default, that first query is considered the primary query. This is an important concept because when Looker matches the data to create the merged results, it matches each added query to the primary query (not to any other added query). So whenever you add a query, you need to include a dimension that can be matched to a dimension in the primary query.

Note that you can change which of the queries is used as the primary query, as described in Switching the Primary Query.

Below is an example set of queries and their merged results:

All of the primary query’s fields are displayed in the merged results, using the primary query’s names for the fields. Which means that if the primary query and an added query use different names for a matching dimension, then it is only the primary query’s dimension name that will be displayed in the results.

For those who are familiar with SQL joins, the Merged Results feature is basically performing a left join: The added query is being left joined into the primary query.

If you aren’t familiar with the idea of a left join, no worries. In practical terms, here’s why it matters which query is the primary query:

  • How field names appear

    For matching fields, the primary query’s field names are used in the merged results, as shown above.
  • How the merged results handle one query not having a matching value

    Discussed in this section below.
  • How the merged results handle one query having multiple matching values

    Discussed in this section below.

What If One Query Doesn’t Have A Matching Data Value?

Another reason the primary query is important is because of the way null values are handled in the matched dimensions:

  • If a row exists in the primary query but not in the additional query, then the added query’s fields will be NULL for that row.

    In the example shown below, the primary query has a row for Maternity, so the merged results show this row. The added query does not have a Maternity row, so any of the fields from the added query will show NULL for Maternity.
  • If a row exists in the added query but not in the primary query, then the row will not show in the results at all.

    In the example below, the added query has two rows for Jeans, but the primary query does not. So this row is not shown in the merged results at all.

In our example, if we switch the primary query to make the added query as the new primary query, we get the merged results shown below.

Note that now we don’t have the Maternity rows because they do not exist in our new primary query. However, now we do have the Jeans rows and those rows show NULL for the dimensions and measures that are only in the query that is added to the primary query.

What If One Query Has Multiple Rows for the Same Value?

Finally, designating the desired primary query is also important because of the way duplicated rows are handled for the matched dimensions. If the added query has two or more rows with values that match a row in the primary query, the primary query row will be duplicated that number of times.

In the example below, the added query has two rows for Dresses. In the merged results, the Dresses row from the primary query is duplicated, once for each of the Dresses rows from the added query:

Note that if you switch the primary query in this case, you would still have two Dresses rows, since the newly-designated primary query has two rows for Dresses. The takeaway is that when merging queries, the results may have more rows than the primary query has — but there will never be fewer rows.

Merging Queries

To merge the results from multiple queries, you need to:

  1. Create the first source query, called the primary query
  2. Add the next source query
  3. Check the merge rules for those queries and run the merge
  4. Optionally, you can:

    • Sort, pivot, and create visualizations for the results
    • Reuse and share the results using the URL
    • Modify the results by editing the source queries or adding source queries

Creating the Primary Query

To merge the results from multiple queries, you start with a beginning query, which is considered the primary query:

  1. Select an Explore from the Explore menu.
  2. Select the dimensions and measures of interest from the Field Picker. Do not pivot any dimensions during this step.

This is all you need to start merging results. However, you can also use some advanced exploring techniques to further refine your query. You can:

  1. Optionally, add filters for the data.
  2. Optionally, include table calculations to create on-the-fly metrics.
  3. Optionally, click Run to see the results of your primary query and to test your filters and table calculations.

Adding the Next Source Query

Once you’ve created your primary Explore, you can add another source query:

  1. In your Explore, click the gear icon.
  2. Select Merge Results. This will open the Choose an Explore modal.
  3. In the Choose an Explore modal, click on the name of an Explore where you will create your next query.

Looker opens the Explore in the Edit Query window, where you can build the new query to be merged into your primary query:

To merge queries, Looker finds dimensions in the queries whose values can be matched. Be sure that your queries contain at least one common dimension whose values can be matched exactly. For example, if both queries have a Date dimension, but one query uses “2017-10-01” as a value and the other query uses “October 2017” as a value, Looker can’t use that dimension to merge the queries.

  1. Select the dimensions and measures of interest from the Field Picker. Be sure to include at least one dimension that will exactly match a dimension in the primary query. Do not pivot any dimensions during this step.
  2. Optionally, include filters to narrow the data.
  3. Optionally, incorporate table calculations to create new fields based on the query fields.
  4. Optionally, click Run to see the results of your primary query and to test your filters and table calculations.
  5. Click Save to merge the query into your primary query.

Checking the Merge Rules and Running the Merge

Looker automatically finds the best dimensions to use for matching the queries and displays these matches in the Merge Rules section. Looker shows how each query will be merged with the primary query:

  1. Review the dimensions that Looker used to match the queries. (See Editing Merge Rules for information on changing these rules.)

  2. Click Run to see the merged query results:

Note that any table calculations from the source queries are displayed as standard dimensions in the merged results.

Using and Modifying the Merged Results

You can use the merged results to:

  • Examine and sort the data
  • Create visualizations
  • Pivot dimensions in the merged results by selecting Pivot from the gear menu in the dimension’s column of the data table. Note that you can’t pivot dimensions in the source queries.

To reuse the merged results, you can:

  • Share the results using the browser URL
  • Bookmark the URL in your browser to run the same merged query again in the future. You can’t save the merged results as a Look or a dashboard tile, but this will be supported in a future Looker release.

If you want to modify the merged results, you can:

  • Merge queries from additional Explores by clicking the Add Query button and following the same steps.
  • Edit the source queries or configure the way the queries are merged. See Editing Merged Results for more information.
  • Create filters by adding those filters in the source queries, either when creating and merging the queries, or by editing the queries from the Merged Results page. Note that you can’t add filter directly to the merged results.

Editing Merged Results

Once you have your merged results, you still have the flexibility to edit the merged queries and the rules used to merge the queries:

Editing the Source Queries

From the Merged Results window, you can go back and edit the source queries by clicking on the query name in the left pane, or by selecting Edit from the query’s gear menu. These options take you back to the Edit Query screen:

From here you can add or remove fields, add table calculations, or change the source query’s filters. Click Save to return to your merged results.

The query’s gear menu also has these options:

  • Rename: Specify a different name to display for the query in the Merged Results window.
  • Make Primary: Make the query the base for the merged results. See Understanding Merged Results to understand the role of the primary query.
  • Delete: Remove the query from the merged results. (If you want to add the query back into the merged results after you’ve deleted it, you can use the Back button in your browser.)

Editing the Merge Rules

When you add a query, Looker automatically finds dimensions it can use to match the added query to the primary query. Each added query must have at least one dimension whose values exactly match up to a dimension in the primary query. Looker displays these matches in the Merge Rules section, showing how each added query will be merged with the primary query:

You can use the Merge Rules section to change or add the dimensions used for matching:

  1. Use the drop-down menu to see other dimension options for matching the data.
  2. If there are additional dimensions that could be used for matching, Looker displays + Add dimension. Click + Add dimension to configure an additional set of dimensions to use in the query merge.
  3. Click on the X if you don’t want to match the data between the two dimensions.

Switching the Primary Query

When merging queries, we start out by creating a single query from a single Explore, and then we add other queries onto that first query. By default, that first query is considered the primary query, but we can designate any query as the primary query by selecting Make Primary from the query’s gear menu:

Note that each added query must have at least one dimension whose values can be matched exactly to a dimension in the primary query.

Also note that when you switch the primary query, the merged results are likely to change. See Understanding Merged Results to understand the role of the primary query.

Conclusion

Whenever possible you should use the data from a single Explore, because your Looker developers have carefully considered how the data from different database tables should be combined together. When needed, though, merging results is a powerful technique that enables you to combine data from multiple Explores and databases.

Still have questions?
Go to Discourse - or - Email Support
Top