Merged Results is a convenient feature for combining data from different Explores quickly and without developing in LookML. Merged results effectively perform 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 you are 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
- 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 page addresses how to troubleshoot this unexpected result.
Example use case
The following example use case is based on a sample e-commerce dataset that contains user and order information. For this example, you 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
The primary query is Users Count grouped by Users City:
Secondary query
The secondary query is Orders Count grouped by Users City and Users State:
The merge rule is set to merge both queries by Users City, the field both queries have in common. Given familiarity with the dataset and expected merged results behavior explained by the documentation linked previously, you know thatin each row, every city should be matched with a state and a count of users. You are expecting that your 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:
Solutions
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 several possible solutions to remedy this outcome, including:
- Sort each source query the same way.
- Increase the source query row limit.
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 — in this case, by Users City ascending.
Sorting the secondary query similarly to the primary query matches the results more accurately in the final merge:
Increase the source query row limit
Similarly to the first solution outlined previously, unexpected nulls 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:
- 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.
- 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.
- Sort the source queries to better match each other.
- Increase the row limit of source queries beyond the default to expose more rows that can be matched and merged.
- If none of the solutions discussed here resolves the behavior, hardcode the join logic into LookML for a more precise result, if possible.