Adding in the Custom Expression field

Looker Q&A Looker
,
1

Hi,

As you can see in the image, I’m trying to make a column that shows the value of 3 other columns combined. The issue is that it does not work if 1 of the 3 columns that are supposed to be added does not exist. I’d like to know how would I make it so it always shows me a value? if 2 of the 3 columns do not exist, then it should give me the value of the 1 column that does; and if 1 column of the 3 does not exist, then it should give me the value of the sum of the other 2. Thank you very much in advance.

${shipments.shipper_line_haul_rate} + ${shipments.shipper_fuel_rate} + ${shipment_accessorials_agg.shipper_accessorial_rate}

2

Try this:

coalesce(${shipments.shipper_line_haul_rate}, 0) + coalesce(${shipments.shipper_fuel_rate}, 0) + coalesce(${shipment_accessorials_agg.shipper_accessorial_rate}, 0)

Coalesce will replace a NULL value with a 0, making it mathematically possible to add to other, existing values

1 Like
3

Thank you so much @Dawid !