Free Trial

Alteryx Designer Desktop Discussions

Find answers, ask questions, and share expertise about Alteryx Designer Desktop and Intelligence Suite.

Sensitivity/Accuracy when using Contains formula

JustynaMZ
7 - Meteor

Hi, I have a table with 2 colums with location naming - unfortunately there was no standardized naming convention for countries. 

Eg. Albania can appear in both columns as ALB or Albania

At the same time we have other locations which can contain 'alb', eg. 'Alba'

 

What I try to get as a result is to find all rows for which value in one of the columns ('Location' or 'Preferred Location') contains 'Albania' or 'ALB'

 

Unfortunately when I used just formula as below I cannot exclude 'Alba' or 'Albertville' as both contain 'alb'

 

if Contains([Preferred Location],"ALB") then 1 else Null() endif
OR
if Contains([Location], "ALB")then 1 else Null()endif
OR
if Contains([Preferred Location],"Albania") then 1 else Null() endif
OR
if Contains([Location], "Albania")then 1 else Null()endif

2 REPLIES 2
apathetichell
19 - Altair

concatenate your values - so you would have a string value like ("Albania","Alb","ALB","Albertville","etc...)

lock your case 

use IN - vs contains

 

"ALB" in(uppercase([concat_values]))

 

This - would give you what you want and omit "Albertville"

CoG
14 - Magnetar

If you need to run this multiple times for different searches, you can use a text input to keep track of the valid search terms (I assumed case-insensitive) then join on search values to find relevant rows.

2023-11-14_16-37_Filtering.png

Labels
Top Solution Authors