IN
Description
IN
returns true for all cases of a column or a value that match any of the values of the match list. An item in the match list can also be another column. Returns false otherwise. The reverse is true for NOT IN
.
Syntax
value/column IN ( value1/column1, ... )
value/column NOT IN ( value1/column1, ... )
Supported types
Supported value types for the match list are STRING, INT, FLOAT, DATE, and any column of that type.
Usage
[NOT] IN
can be used in the following contexts:
CASE WHEN (in the
WHEN
conditions)Pull-Up-Functions (in the filter argument)
BIND_FILTERS (in the filter argument)
CALC_REWORK (in the filter argument)
NULL handling
A match value can also be NULL. A NULL value matches with all NULL values in the match list. NULL values originating from columns in the match list are ignored.
Case sensitivity
Comparison of strings is case-sensitive
Use Cases
IN
can be used for Case Complaints.
Examples
[1] Column and match list contain only integers. | ||||||||||||
| ||||||||||||
|
[2] | ||||||||||||
| ||||||||||||
|
[3] Check if a value is contained in a column. | ||||||||||
| ||||||||||
|
[4] Column contains only integers and match list contains floats and integers. All cases of the column are matched. | ||||||||||
| ||||||||||
|
[5] Column and match list contain only dates. | ||||||||||||
| ||||||||||||
|
[6] Column and match list contain only strings and match list also contains empty string. Two cases of the column are matched. | ||||||||||||||||||
| ||||||||||||||||||
|
[7] Column and match list contain empty string. Empty string is matched. | ||||||||||||||||
| ||||||||||||||||
|
[8] Column and match list contains a NULL value and strings. The IN operator is used for column filtering. | ||||||||||||
| ||||||||||||
|
[9] Column contains a NULL value and 3 strings and match list 3 integers. The IN operator is applied on the COUNT aggregation of the column. One value of the match list is matched. | |||||||||||
| |||||||||||
|
[10] IN with another column in the match list. | ||||||||||||||||
| ||||||||||||||||
|
[11] IN with another column containing NULL in the match list (in CASE WHEN statement). | ||||||||||||||||
| ||||||||||||||||
|