Filter functions

This page goes over the filter functions in Upsolver.

AND

Returns true if all operands are true.

inputs	result
false, false	false
false, true	false
true, false	false
true, true	true

BETWEEN

Checks if a value is between two values.

Inputs

• value

• lowerBound

• upperBound

CONTAINS

Returns true for instances where the left operand contains the right operand.

Inputs

• haystack

• needle

haystack	needle	result
"abcde"	"a"	true
"abcde"	"f"	false

EQUAL_TO

Returns true for instances where the operands are equal.

operand 1	operand 2	result
0	0	true
1	0	false
0	1	false
"a"	"a"	true
"a"	"b"	false

EXISTS

Opt in all the rows that contains the specified field.

input	result
"a"	true
	false

GREATER_THAN

Returns true for instances where operand 1 is greater than operand 2.

operand 1	operand 2	result
0	0	false
1	0	true
0	1	false

GREATER_THAN_OR_EQUAL_TO

Returns true for instances where operand 1 is greater than or equal to operand 2.

operand 1	operand 2	result
0	0	true
1	0	true
0	1	false

IN_SET

Returns true for instances where the value is contained in the given set.

Properties

• Set - Values separated by line breaks

input	set	result
"a"	"a b"	true
"c"	"a b"	false
1	"1 2 3.14"	true
2	"1 2 3.14"	true
3	"1 2 3.14"	false
3.14	"1 2 3.14"	true

IS_DUPLICATE

Returns true if it's not the first time the input value is seen in the data within the specified window size.

This function creates a lookup table that holds all previous keys until the current run. During the current run, it uses in-memory caching to save all keys. A row returns true if the key exists in one of these two caches. However, because the in-memory cache does not work across shards, outputs with shards may contain duplicates that are not marked as such within the run (see workaround below).

Inputs

• value - value to deduplicate

Properties

• Dedup Id

• Window Size - Deduplication window size in minutes

Workaround for shards

The following workaround removes duplicates from the same minute (the interval of the output) when using shards:

-- 1. Create history lookup table "hist"

SELECT "key" AS "key"
  FROM "input"
    GROUP BY "key"
    WINDOW 6 HOURS

-- 2. Create current lookup table "current" (for removing duplicates from the output interval

SET row_position = headers.head.parser_shard_number || '_' || headers.head.parser_row_number;
SELECT "key" AS "key",
       FIRST(row_position) as first_position
  FROM "ad_quality_impressions (production)"
    GROUP BY "key"
    WINDOW 1 MINUTE -- Note: based on the output interval

--Note: position determinites the position of the row in the interval. Can use `headers.head.index` && `headers.head.parser_shard_number`

--3. Create output with the following filter:

SET row_position = headers.head.parser_shard_number || '_' || headers.head.parser_row_number;
SELECT ...
FROM "input"
LEFT JOIN "hist" h on h."key" = "key"
LEFT JOIN "current" c AFTER 1 minute on c."key" = "key"
WHERE h."key" is null AND c.row_position = row_position

LESS_THAN

Returns true for instances where operand 1 is less than operand .

operand 1	operand 2	result
0	0	false
1	0	false
0	1	true

LESS_THAN_OR_EQUAL_TO

Returns true for instances where operand 1 is less than or equal to operand 2.

operand 1	operand 2	result
0	0	true
1	0	false
0	1	true

NOT

Returns true if the value is false.

input	result
true	false
false	true

NOT_EQUAL_TO

Returns true for instances where the operands are not equal.

operand 1	operand 2	result
0	0	false
1	0	true
0	1	true
"a"	"a"	false
"a"	"b"	true

OR

Returns true if at least one of the operands is true.

inputs	result
false, false	false
false, true	true
true, false	true
true, true	true

RANDOM

Returns true for a percentage of items equal to the input.

Inputs

• percent - percentage (in decimal; e.g. 0.2, 0.5, etc) of items to mark as true

XOR

Returns true only if the operands are different from each other.

operand 1	operand 2	result
false	false	false
false	true	true
true	false	true
true	true	false