Left Join, NULL matches as "Other"

(Paul Wadsworth) #1

I have a table joining to a second table in a model.
I have a view, with a column chart, stacked, grouped by a field in the second table.
One of those groupings is the circle with a line through it (is it a 0?), and this is because the value it’s matching on is null in the first table.

It’s correct I want those totals included in the stacked chart, but I want them to say “Other” rather than the circle with a line through it.

Is there somewhere int he LookML I can say if there’s no match to put “Other”? I’d like this to be a field anyone can use


(Izzy) #2

You can do this with a SQL Coalesce— Like what I suggested below, you could just coalesce to ‘Other’:

If it’s a numeric field, you’ll have trouble coalescing it to “other” since that’s a string. You may want to coalesce it to 0, and then in an html: parameter, add an {% if %} statement to turn 0 into ‘Other’.

Note that you can’t just use an html: parameter to turn nulls into something— Once something has returned null, it can’t be operated on with our html capabilities, so you’ll have to coalesce it to something in the SQL regardless.

1 Like

(Paul Wadsworth) #3

Since the other person replied said it wasn’t working for them I thought I’d let you know this worked great for me.

I guess the bit that I need to get my head around is that doing something in the view before it’s joined seems counter intuitive to me, when obviously that’s the way it works in Looker

1 Like