Question

Need to join output of an explore which is pivoted (number of columns may change as per the filters) with another explore

  • 24 November 2020
  • 1 reply
  • 71 views

I have created an explore and based on the filters it provides output in the following format:

 

Dim1 Dim2(String) StringValue
1 key1 val1
1 key2 val2
1 key3 val3

 

And we pivot this to the following format.

 

dim1 key1 key2 key3
1 val1 val2 val3

 

Now, the number of keys can change based on the filters.

I want to join this to another explore which also has dim1 as one of the dimensions, how do I achieve that?

Tried merging but apparently such functionality is being worked upon for over a year now.


1 reply

Hi @kshah, you cannot merge an explore with a pivot already in it. But it is possible to remove the pivot in your primary explore, merge that with a secondary explore, and then pivot in the results of the merge.

In this screenshot, we have a primary explore that gives us the results of all X’s, a second explore that gives us the results of a specific X, and then pivots on a different dimension we have included in both source queries.

We have hit the limits of this approach, but to be fair to Looker we’re already leaning hard into merged queries and what they can do (merged queries are literally the best thing since sliced bread).

So another approach you could investigate (which have used on the rare occasion merges can’t help us), is creating a native derived table from your primary explore, and going from there.

At the top of the documentation for Creating Native Derived Tables is a section titled Using an Explore to Begin Defining Your NDTs. Frankly mind-blowing, when you understand its potential: take your explore, create a table/view from it and even apply filters within the native derived table.

Good luck!

Reply