How to handle null values for dimensions

cristian2
Participant IV

Hi all!
I have a fact and dimensions table. The fact table have a measure. When I group the measure by a dimension from the dimensions table I could get a null value if the dimension doesnt have a key to the fact table. Is there a way of giving this null value a default value?
My result:

Dimension1, Measure
Null, 123
Dim1, 234
Dim2, 567

I do a left join between fact and dimension.

Cheers,
Cris

0 4 12.5K
4 REPLIES 4

Wrapping the field being aggregated by the measure in a coalesce would do this, I think. You could just COALESCE(${field},0), or to whatever default value you’re after.

Where SUM(table.id) returns null, SUM(COALESCE(table.id,0)) would return 0.

cristian2
Participant IV

Hi,
Thanks for the suggestion. I don’t think it works though. The Null-value is on the dimension and not the aggregation itself. I would need to have a key with coalesce which doesn’t work.

Got it, I had it flipped.

The solution here is probably to start with the dimension table, and then left join the fact table to that— Not the other way around, which I think you might have it as.

That way, if you return Dimension1, it’ll return all the possible results, and just return 0’s for the measure where there isn’t a match in the fact table.

Example: There’s a userid 46 in my “User” table, but not in my “userfacts” table.

If you return the user_id dimension from the Userfacts table (the right side of the join), you see the null values you described

If you return the user_id dimension from the User table (the left side of the join), you see the correct values

cristian2
Participant IV

Thanks. Will try that.
Would be good though if there was a possibility to set a default value when value is null. In my case I have one fact table and 21 dimension tables. I would need to change all my joins.

Cheers,
Cris

Top Labels in this Space
Top Solution Authors