Custom expressions workaround null values

Get Help
1

Hello, first post here, but after reading other posts couldn't answer my question anywhere
I have 3 columns - ID, IP, Prod. Only ID could be sometimes blank
I would like to get unique combinations of ID-Prod, but when ID is missing to include IP instead

in simple excel formula, I am looking to do << if ( ID = "", IP & Prod , ID & Prod ) >>

So I couldn't find is null formula in metabase, so I tried finally this
Create a column for IDorIP by << = case ( ID > 0, ID , IP ) - but here is the first issue, there are instances where ID is not blank and still displayes the IP with the above formula.
I can then use concat ( IDorIP , Prod) , but this is already wrong because some ID's are automatically transferred to IP's and is not accurate

Separate note, I tried to do all in one column - concat ( case ( ID > 0, ID , IP ) , Prod ) - but this doesn't even work? Isn't it allowed to do 2 calculations in 1 expression?

So I think tried different approach
LEN = Length ( ID ) - first column
IDorIP = case ( LEN > 0, ID , IP ) - second column - works perfect, unlike the one above
Then third column concat ( IDorIP , Prod ) and I get an error:

Unknown column 'LEN' in 'field list'

The SQL code is this, so I hope someone can help because I am going in circles after couple of hours of trying

SELECT source.id AS id, source.user_id AS user_id, source.user_ip AS user_ip,
source.merchant_id AS merchant_id, source.product_id AS product_id, source.created_at
AS created_at, source.updated_at AS updated_at, source.LEN AS LEN, source.IDorIP2
AS IDorIP2, source.Prod-User AS Prod-User

FROM (SELECT char_length(coupon_activation_history.user_id) AS LEN, CASE WHEN
char_length(coupon_activation_history.user_id) > 0 THEN coupon_activation_history.user_id
ELSE coupon_activation_history.user_ip END AS IDorIP2, concat(CASE WHEN LEN > 0 THEN
coupon_activation_history.user_id ELSE coupon_activation_history.user_ip END,
coupon_activation_history.product_id) AS Prod-User, coupon_activation_history.user_id AS
user_id, coupon_activation_history.user_ip AS user_ip, coupon_activation_history.product_id
AS product_id, coupon_activation_history.id AS id, coupon_activation_history.merchant_id AS
merchant_id, coupon_activation_history.created_at AS created_at,
coupon_activation_history.updated_at AS updated_at FROM coupon_activation_history)
source

LIMIT 1048576

2

Hi @stefanizzi
Have you read this? https://www.metabase.com/learn/questions/custom-expressions
Sounds like you're looking for this:
case(isnull([ID]), concat([IP], [Prod]), concat([ID], [Prod]))

3

Hi @flamber,

Thank you for the input, but unless I am missing something "isnull" is not a valid function in metabase. At least in my version, it doesn't do anything. I don't think it is possible for a light verion of metabase to avoid functions, but the above equation doesn't work for me.

Saw this:

#15922 Repro: isNull function in Custom Columns does not work #15938

I even tried another one of your suggestions from another thread:

sum(coalesce([field], 0))

But I get to the same position as above.

And while the link you posted gives me new ideas, it doesn't really solve my question.
Continue to be stuck unfortunately

4

@stefanizzi I would recommend that you upgrade then. Latest release is 0.40.4:
https://github.com/metabase/metabase/releases/latest

5

I will give it a short. Need to ask my system admin to do it for me.
So did the newer versions add some more custom expressions?
Will keep you posted

6

@stefanizzi There is constantly new things being added. Those functions were added to 0.39, which was released in April: https://www.metabase.com/blog/Metabase-0.39/index.html#improved-custom-expressions
A good rule is to check which version you're using - if it is not the latest, then you might want to upgrade.

7

@flamber
Our system was upgraded and it worked as your original suggestion. Thanks for pointing out the upgrade needed.