How to compare sales from two tables by using same dimension.

[Denis_Sineiko]

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 ?

[Krishna_Shukla]

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
}
}

[Denis_Sineiko]

You can do this but at the end you will have two dim_products to choose from (not a single one)

[zckymc]

Can’t you explore from dim_product, then join sales_tables on to that?

[Denis_Sineiko]

@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 ))