I’m trying to perform a natural join between two views, “inventory_movements_ext” and “order_lines_ext”, each referencing a ‘product_id’, and each with an additional ‘location’ dimension that uses data in each original table to determine a common location label.
The goal is, given a product_id, to pivot by location, and for each location to see the sums of certain quantities from each of the original views.
My first hunch is to perform a natural join between the two views, and to coalesce the two location dimensions into one (since with a natural join between two tables, each row will only ever have one of the two tables’ values, and the other tables’ columns will be NULL).
This would then let me pivot by the coalesced location, and then get the sums I’m looking for.
My initial findings lead me to think that I’ll need to create a derived table to accomplish this, and then to create a view based on the derived table.
I wanted to post here first though, to see if there were facilities in Looker that would help with this, or if there were any alternative modeling suggestions that would get me what I need.