Applies to: CELONIS 4.0 CELONIS 4.2 CELONIS 4.3 CELONIS 4.4
Description
Returns true for all cases of a column that match any of the values of the match list. Returns false otherwise.
Syntax
table.column IN ( value1, ... )
Supported types
Supported value types for the match list are STRING, INTEGER, FLOAT and DATE.
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
Column and match list contain only integers.
Query
Column1
CASE WHEN "Table1"."Column1" IN (1, 3) THEN "Table1"."Column1" ELSE null END
Column contains only integers and match list contains floats and integers. All cases of the column are matched.
Query
Column1
CASE WHEN "Table1"."Column1" IN (1.0, 3) THEN "Table1"."Column1" ELSE null END
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
Input
Table1
| Column1 : DATE |
|---|
Sun Jan 02 2000 00:00:00.000 |
Mon Dec 31 2001 00:00:00.000 |
Sun Apr 16 2006 00:00:00.000 |
Output
Result
| Column1 : DATE |
|---|
Sun Jan 02 2000 00:00:00.000 |
Mon Dec 31 2001 00:00:00.000 |
null |
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)
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
Input
Table1
| Column1 : INT |
|---|
null |
3 |
1 |
-1 |
9223372036854775807 |
-9223372036854775808 |
Output
Result
| Column1 : INT |
|---|
null |
3 |
1 |
null |
9223372036854775807 |
-9223372036854775808 |
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
Column and match list contains only the same floats. All cases of the column are matched.
Query
Column1
CASE WHEN "Table1"."Column1" IN (2.5, 4.0) THEN "Table1"."Column1" ELSE null END
Column contains only floats and match list contains floats and integers. All cases of the column are matched.
Query
Column1
CASE WHEN "Table1"."Column1" IN (2.5, 4) THEN "Table1"."Column1" ELSE null END
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
Input
Table1
| Column1 : STRING |
|---|
| '1' |
| '3' |
| '0' |
| '@' |
| 'aAa' |
| 'bBb' |
Output
Result
| Column1 : STRING |
|---|
| '1' |
| '3' |
| null |
| null |
| 'aAa' |
| null |
Column and match list contain empty string. Empty string is matched.
Query
Column1
CASE WHEN ( "Table1"."Column1" IN ('1', '3', '-10', '') ) THEN "Table1"."Column1" ELSE null END
Column and match list contains a NULL value and strings. The IN operator is used for column filtering.
Query
Filter
FILTER "Table1"."Column1" IN (NULL, '3')
Column1
"Table1"."Column1"
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