Documentation

    Formula

    Calculate new values from existing columns, such as quantity times unit price, or update a column with an expression. Add formulas in order so later calculations can use earlier results.

    Calculate item totals

    Manual Input has four rows with quantity and unit_price. Formula adds total as a DOUBLE using quantity * unit_price.

    The live example needs a desktop screen. The result it produces is shown here, and the explanation and settings below work on any screen.

    Result
    quantityunit_pricetotal
    2510
    3412
    080
    6
    1. Select Add to create a second formula. Set Output column to discounted_total, Output type to DOUBLE and Expression to total * 0.9. This applies a 10% discount using the first formula's result.
    2. The new column contains 9, 10.8, 0 and NULL. Change its Expression to total * 0.8 to apply a 20% discount instead; the results become 8, 9.6, 0 and NULL.
    3. Change the second formula's Output column to total. It now replaces total with the discounted value instead of adding another column.

    When to use

    Use Formula to calculate a new column from existing ones, such as quantity times unit price. For totals across rows, use Summarize.

    Read the output

    The output keeps quantity and unit_price and adds total. The first two rows produce 10 and 12. A quantity of zero produces 0; the missing quantity produces NULL. All four rows stay.

    The steps add discounted_total after total, so it can use the calculated amount. Both columns are DOUBLE, so fractional discounts are kept.

    Replace an existing value

    If an output name matches an existing column, the formula replaces that column. The expression can read the old value, so a second formula named total with total * 0.9 discounts the total from the first formula. The input table doesn't change.

    See the functions reference for functions you can use in expressions.

    To route rows using a condition, use Filter.

    To calculate totals across groups of rows, use Summarize.

    Inputs and outputs

    Inputs

    1 port
    InRequired

    Accepts: Tables, Geospatial data.

    Accepts one connection on this port.

    Outputs

    1 port
    Out

    Configure the block

    Add one formula per result and place dependent calculations after the formulas they use.

    Add

    Add a formula. Each entry creates or replaces one output column.

    Output column

    Name the result. A new name appends a column; an existing name replaces that column's values.

    Output type

    Choose VARCHAR for text, BIGINT or INTEGER for whole numbers, DOUBLE or FLOAT for fractional numbers, DECIMAL for exact numbers, BOOLEAN for true/false, DATE, TIME, TIMESTAMP or TIMESTAMP WITH TIME ZONE. Results that can't be converted to the selected type become NULL. Default: BIGINT.

    Decimal places

    Digits kept after the decimal point. Values round to this many places.

    Total digits

    Maximum digits in the value, including decimal places. Values that don't fit become NULL.

    Expression

    Enter a DuckDB SQL expression using input columns or earlier formula results. Enter only the expression, without a leading equals sign or a SELECT statement.

    Formula FAQs

    Can a formula use another formula's result?
    Yes. Formulas run in list order. A later formula can use a column created or replaced by an earlier one. Referencing a column that doesn't yet exist causes an error.
    What happens to missing values?
    It depends on the expression. In this example, multiplying a missing quantity by unit_price produces NULL. Use coalesce(quantity, 0) * unit_price only if a missing quantity should count as zero.
    Does an invalid expression become NULL?
    No. Invalid SQL or an unknown column causes an error. A valid expression can still give NULL if its result can't be converted to the selected Output type.
    How do I write text values and column names?
    Use single quotes for text values. Put column names containing spaces in double quotes. Simple names such as quantity need no quotes.

    Prepare your data. Build it in Strata.

    Strata opens in your browser, with no account.