Applies to: CELONIS 4.0 CELONIS 4.2 CELONIS 4.3 CELONIS 4.4 CELONIS 4.5 CELONIS 4.6
Description
IN
returns true for all cases of a column that match any of the values of the match list.
Returns false otherwise. The reverse is true for NOT IN
.
Syntax
table.column IN ( value1, ... )
table.column NOT IN ( value1, ... )
Supported types
Supported value types for the match list are STRING, INTEGER, FLOAT and DATE.
Usage
[NOT] IN
can be used in the following contexts:
Null handling
A match value can also be NULL. A NULL value matches with all cases of a column that are null.
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] Match list is parameterized with one parameter and IN operation is stored as a KPI. Stored KPI is called with one
integer value which results in only one case of the column being matched.
Query
KPI "SAVED FORMULA"
CASE WHEN "Table1"."Column1" IN ({p1}) THEN "Table1"."Column1" ELSE 0 END
Column1
KPI("saved formula", 1)
[6] Column and match list contain positive and negative integers. All non-null cases of the column are matched.
Query
Column1
CASE WHEN ( "Table1"."Column1" IN (1, 3, -10, 9223372036854775807, -9223372036854775808 ) ) THEN "Table1"."Column1" ELSE null END
[7] Column contains only NULL values and match list only non-NULL values. No cases of the column are matched.
Query
Column1
CASE WHEN ( "Table1"."Column1" IN (1, 3, -10, 2147483647, -2147483648 ) ) THEN "Table1"."Column1" ELSE null END
[10] 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
[13] 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