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.
Formula interactive example
Loading interactive example…
The live example needs a desktop screen. The result it produces is shown here, and the explanation and settings below work on any screen.
| quantity | unit_price | total |
|---|---|---|
| 2 | 5 | 10 |
| 3 | 4 | 12 |
| 0 | 8 | 0 |
| 6 |
- Select Add to create a second formula. Set Output column to
discounted_total, Output type toDOUBLEand Expression tototal * 0.9. This applies a 10% discount using the first formula's result. - The new column contains 9, 10.8, 0 and NULL. Change its Expression to
total * 0.8to apply a 20% discount instead; the results become 8, 9.6, 0 and NULL. - Change the second formula's Output column to
total. It now replacestotalwith 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.