For example, I have Table gross sales per product (This year and Last Year) and Product dimension.
E.G.
explore: sample_explorer {
join: dim_product {
sql_on: ${sales_table_1.plu} = ${dim_product.plu};;
relationship: many_to_one
}
join: sales_table_1 {
sql_on: ${sample_explorer.key} = ${sales_table_1.key};;
relationship: many_to_one
}
join: sales_table_2 {
sql_on: ${sample_explorer.key} = ${sales_table_2.key};;
relationship: many_to_one
}
}
How to JOIN sales_table_2 to dim_product by using single dim_product???
It is easy to do by using CTE, but will looker have some work around it ?
I think using “from: tablename” keyword we can join same table two times.
Reference - https://docs.looker.com/reference/explore-params/from-for-join
explore: sample_explorer {
join: dim_product1 {
from: dim_product
sql_on: ${sales_table_1.plu} = ${dim_product1.plu};;
relationship: many_to_one
}join: sales_table_1 {
sql_on: ${sample_explorer.key} = ${sales_table_1.key};;
relationship: many_to_one
}join: sales_table_2 { sql_on: ${sample_explorer.key} = ${sales_table_2.key};; relationship: many_to_one
}
join: dim_product2 {
from: dim_product
sql_on: ${sales_table_2.plu} = ${dim_product2.plu};;
relationship: many_to_one
}
}
You can do this but at the end you will have two dim_products to choose from (not a single one)
Can’t you explore from dim_product, then join sales_tables on to that?
@zckymc In theory yes, but unfortunately the main table is cross join (store / per day / per hour) if I will add as well “per product” , the table will too huge ))