Skip to main content

not in

The not in match filter returns rows that do not contain a match of one or more specified values.

Syntax#

columnName not in valueList
  • columnName - the column the filter will search for non-matching values.
  • valueList - the set of values to remove. This supports:
    • a comma-separated list of values: A not in X, Y, Z. The filter will return true for all rows where the value in column A is not equal to X, Y, and Z.
    • a java array: A not in X. The filter will return true for all rows where A is not equal to every element of the java array X.
    • a java.util.Collection: A not in X. The filter will return true for all rows where A is not equal to every element of the collection X.
    • all other types: A not in X. The filter will return true for all rows where A is not equal to X.

Examples#

The following example returns rows where Color is not in the comma-separated list of values.

from deephaven.TableTools import newTable, stringCol, intColfrom deephaven.conversion_utils import NULL_INT
source = newTable(    stringCol("Letter", "A", "C", "F", "B", "E", "D", "A"),    intCol("Number", NULL_INT, 2, 1, NULL_INT, 4, 5, 3),    stringCol("Color", "red", "blue", "orange", "purple", "yellow", "pink", "blue"),    intCol("Code", 12, 13, 11, NULL_INT, 16, 14, NULL_INT),)
result = source.where("Color not in `blue`, `orange`")

The following example returns rows where Number is not in an array of values.

from deephaven.TableTools import newTable, stringCol, intColfrom deephaven.conversion_utils import NULL_INT
array = [2, 4, 6]
source = newTable(    stringCol("Letter", "A", "C", "F", "B", "E", "D", "A"),    intCol("Number", NULL_INT, 2, 1, NULL_INT, 4, 5, 3),    stringCol("Color", "red", "blue", "orange", "purple", "yellow", "pink", "blue"),    intCol("Code", 12, 13, 11, NULL_INT, 16, 14, NULL_INT),)
result = source.where("Number not in array")

The following example returns rows where Number is not in an array of values or Code is in an array of values.

from deephaven.TableTools import newTable, stringCol, intColfrom deephaven.conversion_utils import NULL_INT
numberArray = [2, 4, 6]codeArray = [10, 12, 14]
source = newTable(    stringCol("Letter", "A", "C", "F", "B", "E", "D", "A"),    intCol("Number", NULL_INT, 2, 1, NULL_INT, 4, 5, 3),    stringCol("Color", "red", "blue", "orange", "purple", "yellow", "pink", "blue"),    intCol("Code", 12, 13, 11, NULL_INT, 16, 14, NULL_INT),)
result = source.whereOneOf("Number not in numberArray", "Code in codeArray")

The following example returns rows where Color is not in a collection of values.

from deephaven.TableTools import newTable, stringCol, intColfrom deephaven.conversion_utils import NULL_INT
myList = ["pink", "purple", "blue"]
source = newTable(    stringCol("Letter", "A", "C", "F", "B", "E", "D", "A"),    intCol("Number", NULL_INT, 2, 1, NULL_INT, 4, 5, 3),    stringCol("Color", "red", "blue", "orange", "purple", "yellow", "pink", "blue"),    intCol("Code", 12, 13, 11, NULL_INT, 16, 14, NULL_INT),)
result = source.where("Color not in myList")

Related documentation#