Feature request - SQL WHERE but only if joined

Dawid
Participant V

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.

0 2 69
2 REPLIES 2

n_davis
Participant V

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

IanT
Participant V

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.

Top Labels in this Space
Top Solution Authors