Sometimes I have two simple views connected to each other but since it’s LEFT_OUTER, there may be some entity records without the equivalent on the right side.
However, if I use only fields from the right side of the join (imagine aggregation, history, enrichment table) then I may get a NULL in my dimension.
Ideally I would like to be able to set up a conditional join that if any fields from the right side are used, there can’t be any nulls.
explore: users {
join: users_daily {
type: left_outer
relationship: one_to_many
sql_on: ${users.user_id} = ${users_daily.user_id} ;;
sql_where_if_used: ${users_daily.user_id} IS NOT NULL ;;
}
}
Of course I can’t change it into INNER because I will lose the left side. I could create separate explore for the right side but I think with such small feature, I could avoid a lot of duplicated code.
You should be able to do a sql_where: ${users_daily.user_id} IS NOT NULL ;;
May need to do an equal_null in the sql_on like below (depending on which connection type you use, this is from Snowflake):
sql_on: equal_null(${users.user_id} , ${users_daily.user_id}) ;;
I think you wrote an “any” in your explanation (last sentence) where you mean “only”. (And maybe: sql_where_if_only_this_used)
Its fairly niche and could potentially be cause confusion to users with the need for multiple similar measures (cnt active today/cnt users) rather than splitting by dimension (is active on day) and then a whole load of nulls showing up if user puts in a user dimension. Can understand how you would find it useful tho.