community
cancel
Showing results for 
Search instead for 
Did you mean: 

Alteryx designer Discussions

Find answers, ask questions, and share expertise about Alteryx Designer.
Upgrade Alteryx Designer in 10 Steps

Debating whether or not to upgrade to the latest version of Alteryx Designer?

LEARN MORE
SOLVED

Difference between In function and Contains function

Hi All,

 

I am just looking for some general information on the difference between the In function and the Contain function. I use both frequently and sometimes one will work but not the other. Also, sometimes Contains will return an error stating that it requires 2 or 3 inputs- what exaclty does this mean? 

 

Thanks

ACE Emeritus
ACE Emeritus

I'm not familiar with an 'IN' function in Alteryx.  Is it part of some extended library of functions?

Alteryx
Alteryx

I usually look at is as Contains looks for the occurrence of a particular string within a string.  So Contains([variable], '123') matches '123456', but not '456789'..  I use the IN statement, mostly in the interface tools, to match several strings.  [variable] IN ('1','2','3') would match any of the options (1. 2, 3 or any mix of them), not look for all.

Scott Jones
Strategic Sales Engineer
Alteryx, Inc.
Highlighted
Alteryx Partner

HI,

 

the IN functions is in the Operator group and it looks at the entire field. It's the same as using [ = OR ].

 

Le'ts say you have a field:

 

[Column 1]

1151

1

2222

2

3456

 

[Column 1] in (1,2)  is the same as [Column 1] = 1 or [Column 1] = 2
It looks for the number 1 or the number 2

 

[Column 1]

1151 --> False

1 --> True

2222 -- False

2 --> True

3456 --> False

 

If you're dealing with numbers. If you're dealing with strings,

[Column 1] in ("1", "2")  is the same as [Column 1] = "1" or [Column 1] = "2"

It looks for the string "1" or "2"

 

[Column 1]

1151 --> False

1 --> True

2222 -- False

2 --> True

3456 --> False

 

The contains function is in the String group and can only be used with strings, and it looks at any part of the strings, not the entire string:

 

Contains([Column 1], "1")
It checks if the character "1" is contained

 

[Column 1]

1151 --> True

1 --> True

2222 -- False

2 --> False

3456 --> False

 

Contains([Column 1], "1")  or Contains([Column 1], "2")
It checks if the character "1" or the character "2" is contained

[Column 1]

1151 --> True

1 --> True

2222 -- True

2 --> True

3456 --> False

 

Contains --> String group --> Only strings, looks for any part of the field

IN --> Operator group --> Both strings and numbers, looks for the entire field

Meteoroid

Hello Federica,

 

First of all, thank you for your explanations on the difference between contains and  in function. Very well explained!

 

I have another question regarding the Contains function. Do you know if we can use wildcards in Contains function?

 

I have this example:

 

If Contains([title], " Review") THEN
"1"
ELSEIF
Contains([title], "Profile") THEN
"2"
ELSEIF
Contains([title], "PEST.*") THEN
"3"
ELSE

"10"
ENDIF

 

I am asking myself if the wildcard works here because it seems that not. (the blue part of my formula). I want to find PEST or PESTLE.

 

I know that I could add another ELSEIF with the other version of PESTLE but I am really interested in finding a way of using wildcard because I need this for other cases.

 

Thank you for any help on this,

 

 

Moderator
Moderator

@stef_bolo - you can't use a wildcard, as anything between " " is treated as a literal string in the contain function (unlike the Regex function below), but luckily contains([title],"PEST") will match both PEST and PESTLE, or anything else with PEST in it, such as TEMPESTUOUS.

 

An alternative to Contains would be regex, in which you can use a wildcard to only match things such as PEST and PESTLE but not TEMPESTUOUS:

REGEX_Match([Field1], "PEST.*")

AnudwX

 

Sophia Fraticelli
Customer Support Engineer
Meteoroid

In addition to CONTAINS or IN function is it possible to use a wildcard in a function?   For example, If wanted to look for text that started with "primary", in an Access  query that would say "primary*".   This would return any text like primary-DR, primary-NAS, or primary-Thick.  However it would not return a record like iNAS-primary or Thin-primary.   Is this possible? 

Alteryx
Alteryx

I would suggest trying the Starts with function, StartsWith([Field], 'Primary'), or you could do a Left([Field], 7) = 'Primary'

 

If the search is in the middle of the string, you would need to do a Substring.

 

Scott Jones
Strategic Sales Engineer
Alteryx, Inc.
Labels