Merged Results is a convenient feature for combining data from different Explores quickly and without developing in LookML. Merged Results effectively performs a left join between the primary query and secondary queries — meaning that field names, matching values, and the ending results table are dependent upon how data from the secondary queries match to the primary.

This can potentially cause unexpected results when performing merges between Explores. The merged results documentation addresses a few of these conditions, such as when One Query Doesn't Have a Matching Data Value, and when One Query Has Multiple Rows for the Same Value.

However, what do you do when you expect a value from a secondary query to match a value in the primary query, but the end result displays null values?

This article addresses how to troubleshoot this unexpected result.

Example Use Case

The following example use case is based on a sample e-commerce dataset containing user and order information. I want to merge one query, a count of users per city (Users Count grouped by Users City), with a secondary query, a count of orders (Orders Count) grouped by Users City and Users State:

Primary Query

Secondary Query

The merge rule is set to merge both queries by Users City, the field both queries have in common. Given familiarity with my dataset and expected merged results behavior explained by the documentation linked above, I know that, in each row, every city should be matched with a state and a count of users. I am expecting that my resulting merged results will match all values and display no null values.

However, there are nulls in the results. Over half of the cities are not matched with a state or a count of orders:

Don't panic. If you are certain there are matching values in your data (try running a separate query to confirm that this is the case), there are two possible solutions to remedy this outcome.

Solutions

Sort Each Source Query the Same Way

Because merged results are based on Explores, which are limited to 500 rows by default, sometimes the query results you are merging are not included in the final result.

To fix this, you can edit and sort your individual source queries to better match each other. 

In the example use case, the primary query is sorted by Users City ascending. The secondary query is not. To better match the results of the two queries, you can sort the secondary query the same way as the primary query:

Sorting the secondary query similarly to the primary query matches the results more accurately in the final merge:

However, there still appear to be unexpected null values in the secondary query columns in the results table:

In this case, you can apply a second troubleshooting method.

Increase Source Query Row Limit

Similarly to the first solution outlined above, this unexpected behavior can be caused by the row limits set in the source queries. Specifically in this case, the secondary query (limited by the 500 row default) does not have enough rows to match all of the rows generated by primary query, causing null results to be displayed in the final merge.

To increase the number of rows in the secondary query to match the primary query, you can increase the secondary query row limit:

This results in more possible rows to match to the primary query and fewer null values in the secondary query columns: 

Summary

When faced with unexpected merged results, you can take the following steps to troubleshoot:

1. Select the Clear Cache and Refresh option from the Explore's gear menu to ensure the query is pulling the most up-to-date results.
2. Confirm that there are matching values between source queries where nulls are displayed, as outlined in the What If One Query Doesn't Have a Matching Data Value? section of the Merged Results documentation.
3. Sort the source queries to better match each other.
4. Increase the row limit of source queries beyond the default to expose more rows that can be matched and merged.
5. If none of the above solutions resolves the behavior, hard-code the join logic into LookML for a more precise result, if possible.