Versions Compared

Old Version 1

changes.mady.by.user Max Kohl

Saved on

compared with

New Version Current

changes.mady.by.user David Becher

Saved on

Key

  • This line was added.
  • This line was removed.
  • Formatting was changed.
Applies to:  
Status
colourGreen
titleCelonis 4.0
 
Status
colourGreen
titleCelonis 4.2
 
Status
colourGreen
titleCelonis 4.3
 
Status
colourGreen
titleCelonis 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


Code Block
languagetext
themeDJango
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.



Panel
titleQuery
Column1


Code Block
languagetext
themeDJango
CASE WHEN "Table1"."Column1" IN (1, 3) THEN "Table1"."Column1" ELSE null END




Panel
titleInput
Table1


Column1 : INT
1
3
5




Panel
titleOutput
Result


Column1 : INT
1
3
null





Column contains only integers and match list contains floats and integers. All cases of the column are matched.



Panel
titleQuery
Column1


Code Block
languagetext
themeDJango
CASE WHEN "Table1"."Column1" IN (1.0, 3) THEN "Table1"."Column1" ELSE null END




Panel
titleInput
Table1


Column1 : INT
1
3




Panel
titleOutput
Result


Column1 : INT
1
3





Column and match list contain only dates.



Panel
titleQuery
Column1


Code Block
languagetext
themeDJango
CASE WHEN "Table1"."Column1" IN ({t 946771200000},{t 1009756800000},{t 1245145600000}) THEN "Table1"."Column1" ELSE null END




Panel
titleInput
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




Panel
titleOutput
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.



Panel
titleQuery
KPI "SAVED FORMULA"


Code Block
languagetext
themeDJango
CASE WHEN "Table1"."Column1" IN ({p1}) THEN "Table1"."Column1" ELSE 0 END


Column1


Code Block
languagetext
themeDJango
KPI("saved formula", 1)




Panel
titleInput
Table1


Column1 : INT
1
2
3




Panel
titleOutput
Result


Column1 : INT
1
0
0





Column and match list contain positive and negative integers. All non-null cases of the column are matched.



Panel
titleQuery
Column1


Code Block
languagetext
themeDJango
CASE WHEN ( "Table1"."Column1" IN (1, 3, -10, 9223372036854775807, -9223372036854775808 ) ) THEN "Table1"."Column1" ELSE null END




Panel
titleInput
Table1


Column1 : INT
null
3
1
-1
9223372036854775807
-9223372036854775808




Panel
titleOutput
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.



Panel
titleQuery
Column1


Code Block
languagetext
themeDJango
CASE WHEN ( "Table1"."Column1" IN (1, 3, -10, 2147483647, -2147483648 ) ) THEN "Table1"."Column1" ELSE null END




Panel
titleInput
Table1


Column1 : INT
null
null




Panel
titleOutput
Result


Column1 : INT
null
null





Column and match list contains only the same floats. All cases of the column are matched.



Panel
titleQuery
Column1


Code Block
languagetext
themeDJango
CASE WHEN "Table1"."Column1" IN (2.5, 4.0) THEN "Table1"."Column1" ELSE null END




Panel
titleInput
Table1


Column1 : FLOAT
2.5
4.0




Panel
titleOutput
Result


Column1 : FLOAT
2.5
4.0





Column contains only floats and match list contains floats and integers. All cases of the column are matched.



Panel
titleQuery
Column1


Code Block
languagetext
themeDJango
CASE WHEN "Table1"."Column1" IN (2.5, 4) THEN "Table1"."Column1" ELSE null END




Panel
titleInput
Table1


Column1 : FLOAT
2.5
4.0




Panel
titleOutput
Result


Column1 : FLOAT
2.5
4.0





Column and match list contain only strings and match list also contains empty string. Two cases of the column are matched.



Panel
titleQuery
Column1


Code Block
languagetext
themeDJango
CASE WHEN ( "Table1"."Column1" IN ('1', '3', '-10', '', 'aAa', 'bbb') ) THEN "Table1"."Column1" ELSE null END




Panel
titleInput
Table1


Column1 : STRING
'1'
'3'
'0'
'@'
'aAa'
'bBb'




Panel
titleOutput
Result


Column1 : STRING
'1'
'3'
'
null
'
'
null
'
'aAa'
'
null
'





Column and match list contain empty string. Empty string is matched.



Panel
titleQuery
Column1


Code Block
languagetext
themeDJango
CASE WHEN ( "Table1"."Column1" IN ('1', '3', '-10', '') ) THEN "Table1"."Column1" ELSE null END




Panel
titleInput
Table1


Column1 : STRING
''
'2'
'
null
'
'3'
'4'




Panel
titleOutput
Result


Column1 : STRING
''
'
null
'
'
null
'
'3'
'
null
'





Column and match list contains a NULL value and strings. The IN operator is used for column filtering.



Panel
titleQuery
Filter


Code Block
languagetext
themeDJango
FILTER "Table1"."Column1" IN (NULL, '3')


Column1


Code Block
languagetext
themeDJango
"Table1"."Column1"




Panel
titleInput
Table1


Column1 : STRING
'
null
'
''
'3'
'4'




Panel
titleOutput
Result


Column1 : STRING
'
null
'
'3'





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.



Panel
titleQuery
Column1


Code Block
languagetext
themeDJango
CASE WHEN COUNT( "Table1"."Column1" ) IN (1, 2, 3) THEN 1 ELSE 0 END




Panel
titleInput
Table1


Column1 : STRING
'
null
'
'1'
'2'
'3'




Panel
titleOutput
Result


Column1 : INT
1