Formulae function for table instead of line by line

mroker

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

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

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

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

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.