Calculated field - "Contains" function using two columns

Question & Answer
1

Hi,
I’m trying to compare two fileds in a calculated field using contains function, but the function throws error for substring as “argument doesnt not have correct type”. Is there an alternate to contains or am I missing something here?

distinct_countif({id},contains({Col-1},{col-2})) ,
The same fucntion works when I replace the substring with literal value - “ABC”

Thanks,

1 Like
2

Can you wrap both the col-1 and col-2 in a toString?

distinct_countif({id},contains(toString({Col-1}),toString({col-2})))

3

That’s the first option I tried but it still throws the same error for col-2 substring.

4

Can you check what your col-2 data type is?

image

5

Max, it is “string” datatype.

6

Hmm, from further looking into it. It looks like you can’t do this with a contain.

I would suggest looking into locate.

locate({attributes_url},{client_id[users]},1) works for me

You would just check if it’s greater than 1

1 Like
7

Hi @arunvg,
We hope the reply from @Max worked for you.
Let us know if this is resolved. And if it is, please help the community by marking the reply as a “Solution.”
Many Thanks,
Andrew

8

Locate isn’t efficient for my use-case although it sounds like an alternate, I was not able to test it.
Instead I got around creating a parameter in QS and used the contains option in the custom filter.

2 Likes