Community Spring Cleaning week is here! Join your fellow Maveryx in digging through your old posts and marking comments on them as solved. Learn more here!

Alteryx Designer Desktop Discussions

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

Difference between In function and Contains function

bryanmason19
7 - Meteor

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

7 REPLIES 7
pcatterson
11 - Bolide

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

scottj
Alteryx Alumni (Retired)

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.
Federica_FF
11 - Bolide

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

stef_bolo
6 - 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,

 

 

SophiaF
Alteryx
Alteryx

@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
Senior Solutions Architect
Alteryx, Inc.
resqui
6 - 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? 

scottj
Alteryx Alumni (Retired)

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