Applies to: CELONIS 4.0 CELONIS 4.2 CELONIS 4.3 CELONIS 4.4 CELONIS 4.5 CELONIS 4.6 CELONIS 4.7
Description
IN
returns true for all cases of a column that match any of the values of the match list. An
item in the match list can also be another column (Since: CELONIS 4.7).
Returns false otherwise. The reverse is true for NOT IN
.
Syntax
table.column IN ( value1/column1, ... )
table.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) - FILTER
- 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
Examples
[4] Column and match list contain only dates.
Query
Column1
CASE WHEN "Table1"."Column1" IN ( {t 946771200000 } , {t 1009756800000 } , {t 1245145600000 } ) THEN "Table1"."Column1" ELSE null END
[5] Column and match list contain only strings and match list also contains empty string. Two cases of the column
are matched.
Query
Column1
CASE WHEN ( "Table1"."Column1" IN ( '1' , '3' , '-10' , '' , 'aAa' , 'bbb' ) ) THEN "Table1"."Column1" ELSE null END
[8] 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.
Query
Column1
CASE WHEN COUNT ( "Table1"."Column1" ) IN ( 1 , 2 , 3 ) THEN 1 ELSE 0 END
[10] IN with another column containing NULL in the match list (in CASE WHEN statement).
Query
Column1
CASE WHEN "Table1"."Column1" IN ( "InTable"."Column1" ) THEN 'match' ELSE 'no match' END