Formulae function for table instead of line by line

mroker · May 2023

I have two tables. The first table i have a list of telephone numbers. The second table, a list of the first 3 digits of the number. I want to qualify the first with the next. Here is my formulae:

case when SUBSTR(Phone,4,3) not in(NXX) then 'no' else 'yes' END

so this is fine in that it qualifies line by line. However, I want it to compare against the whole table and validate. How can this be done in Domo?

MarkSnodgrass · May 2023

It's going to compare line by line. How did you join the two tables together? If you are essentially want to "search" the nxx column to see if there is a match with your substring value, you would need to a cartesian join that would repeat all the nxx rows for each phone number and then you would filter where there is a match.

MarkSnodgrass · May 2023

Your substring is looking at digits 4,5, and 6 in a phone number, not the first 3. Wouldn't you want it to be

CASE WHEN LEFT(phone,3) = threedigitfield THEN 'Same' ELSE 'Different' END

mroker · May 2023

i want the 4,5,6 number as the first 3 are the area code. But I guess what i wanted to know is…. when i run this, does it look just at the line or look at the whole column of nxx?

MarkSnodgrass · May 2023

It's going to compare line by line. How did you join the two tables together? If you are essentially want to "search" the nxx column to see if there is a match with your substring value, you would need to a cartesian join that would repeat all the nxx rows for each phone number and then you would filter where there is a match.

Formulae function for table instead of line by line

Best Answer

Answers

Categories