Merged Queries not showing expected results

[Bharat_kumar]

Hi Team,

I have a requirement to merge two views in looker and its not working as expected. I want to understand how the looker joins these two views whether its based on column header names or column values.

Here is my example.

View 1 Category	Count
A	100
B	200
C	150

View 2 Category	Count
A	10
B	20

Expected Merge View:

Category	View 1 Count	View 2 Count
A	100	10
B	200	20
C	150	0

Actual Merge View:

Category	View 1 Count	View 2 Count
A	100	null
B	200	null
C	150	null

What is missing here? Does the merging feature expect the same number of records on both the views? Why its not able to join the records even though both the views have the category field.

@izzymiller
@cole_elliott

[Cyril_MTL_Analy]

On top of my mind:

• The category dimension does not have the same type/values in both views hence Looker cannot match them
• You’re limiting your view 2 and not enough data surfaces in the merge to be displayed (limits are applied before the merge).

[Bharat_kumar]

Does that mean the overall count of categories have to be same for both the views? in my view 2, the categories list can be less than view 1.

[cole_elliott]

Hey @Bharat_kumar,

No, the count of records returned in the query from view 2 that you’re using to merge with the results of the query from view 1 don’t have to match.

However, @Cyril_MTL_Analy’s point is if you’re applying filters in the query from view 2 prior to merging such that your result set doesn’t contain records for Category A and Category B, then you won’t get view 2 counts for any categories.

Are you sure, too, that you’re merging the queries both on the Category field? That could also result in null values for the View 2 Count.

[Bharat_kumar]

Hwy @cole_elliott : I am pretty sure the view 2 is having categories which are in view 1 but is a subset of view 1 categories. In the screen where it shows both the queries , when i click edit on secondary query and Run, its showing the categories and count but its not showing in the merged view.

Yes, in the merge by drop downs, i am selecting category field in both the views. Not sure what else i am missing here. Thanks.

[cole_elliott]

Sorry @Bharat_kumar but I’m out of ideas on what it could be. My recommendation would be to get in touch with Looker support directly and see if they can help you sort it out. @Cyril_MTL_Analy’s first point may be the culprit, or some other discrepancy in your model file or the individual view files.

[Cyril_MTL_Analy]

Check in SQL runner if you can join the two tables using that field.

Maybe there is formatting error somewhere!

[Bharat_kumar]

I did check the types of both the fields and both are string. let me try joining in sql runner,

[Bharat_kumar]

@cole_elliott @Cyril_MTL_Analy : Oh man… its my bad… in the second view the category had trailing spaces… So after i did a trim it worked… Thanks guys for your support.

[cole_elliott]

Great @Bharat_kumar! Glad you were able to figure it out!

[Cyril_MTL_Analy]

Glad it worked out!