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.
Hi @kshah1, 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!