Skip to main content
Version: Java (Groovy)

How to use formulas

This guide will show you how to work with formulas in your query strings.

Formulas can be used in two contexts:

  1. To filter data, such as with where, whereIn, etc. In this case, they must return booleans (e.g., "x%3 == 1").
  2. To assign values, such as with update, view, etc. In this case, a result is set equal to a column name (e.g., "A = x%3").

Regardless of the formula usage, there are common language features used to construct a formula:

Filter data​

You can use formulas to filter your data to show only what you want using where.

Boolean formulas in filter methods are also known as conditional filters. These formulas are designed to return a boolean value (true or false) to narrow data sets to only desired values.

In the following example, operators are used to limit values in result tables.

source = newTable(
        stringCol("X", "A", "B", "C", "D", "E", "F", "G"),
        intCol("Y", 1, 2, 3, 4, 5, 6, 7),
        intCol("Z", 2, 3, 1, 2, 3, 1, 2)
)

result = source.where("Y > 5")

Assign data​

You can use formulas to add and assign new data. When a formula returns a value, it can be used with selection methods to create columns.

In the following example, operators are used to create new columns of values based on prior data.

source = newTable(
        stringCol("X", "A", "B", "C", "D", "E", "F", "G"),
        intCol("Y", 1, 2, 3, 4, 5, 6, 7),
        intCol("Z", 2, 3, 1, 2, 3, 1, 2)
)

result = source.update("Sum = Y + Z")

More complex formulas​

Formulas can be used with the full power of the query language, such as with variables, functions, and objects.

In this example, functions and objects are used to both filter and assign values.

f = { int a, int b -> a * b }

class MyObj {
    public int a, b, c

    MyObj(int a, int b, int c) {
        this.a = a
        this.b = b
        this.c = c
    }

    int compute(int value1){
        return  value1
    }
}

obj = new MyObj(1, 2, 3)

source = newTable(
        stringCol("X", "A", "B", "C", "D", "E", "F", "G"),
        intCol("Y", 1, 2, 3, 4, 5, 6, 7),
        intCol("Z", 2, 3, 1, 2, 3, 1, 2)
)

result = source.where("(int)f(Y, Z) > 9").update("A = obj.a", "B = obj.compute(Y)")