Table of Contents

Calculated

Derives a value from other columns of the same row.

Typical uses are a full name derived from a first and last name, a line total derived from a quantity and a unit price, a status derived from a threshold, and a distance between two generated points. Any column a calculation reads may be hidden, which generates the inputs a calculation requires without writing them to the output.

A calculation is a tree of operations. Separate operation sets are provided for arithmetic, text, dates and times, geometry, and conditional logic. Any operation's input may itself be an operation, so compound expressions are built by nesting rather than by a dedicated operation for each combination.

Sub-field: value

Each JSON block on this page is either a single field object or, where a calculation reads a column produced by another field, an array of consecutive field objects in the order they appear in a table's fields array. See Building a Request for the full request structure.

Properties

Property Type Default Description
calculation Calculation required The tree of operations to evaluate. See The three kinds of node.
map object required Maps value to a destination column and data type. The data type also determines how the calculated result is converted before it is written. See The result's data type.
nullPercentageMax number 0 Upper bound (0-100) of the percentage of rows that should be null. See nullPercentageMin.
nullPercentageMin number 0 Lower bound (0-100) of the percentage of rows that should be null. The rate is fixed once for the whole field from this range, not recalculated per row. Set equal to nullPercentageMax for an exact rate. Applied in addition to any nulls the calculation itself produces.
seed integer none Not accepted. A calculation involves no random selection, so a request that sets seed is rejected.
unique boolean false Must be false. A calculation returns the same result for the same inputs, so regenerating a duplicate row cannot resolve it. Set unique on a column the calculation reads instead.

The three kinds of node

A calculation is a tree of nodes. Each node is exactly one of the following:

  • A column, read from the current row: { "column": "first_name" }.
  • A literal, a fixed value: { "literal": 100 }, { "literal": "Auto" }, { "literal": true }.
  • An operation, evaluated from inputs, each of which is itself a node: { "operation": "Add", "inputs": [...] }.

Setting more than one of column, literal, and operation on a single node is rejected, as is setting none of them. Each operation accepts only the properties listed for it in the sections below. A property an operation does not read, such as places on Upper, is rejected rather than ignored, consistent with the handling of unrecognized properties elsewhere in a request.

The following line total applies Round to the result of Multiply, whose own inputs are two columns produced by the fields before it:

[
  {
    "type": "Number",
    "mode": "Whole",
    "min": 1,
    "max": 10,
    "map": { "value": { "column": "quantity", "dataType": "Integer" } }
  },
  {
    "type": "Number",
    "mode": "Real",
    "min": 1,
    "max": 100,
    "scale": 2,
    "precision": 5,
    "map": { "value": { "column": "unit_price", "dataType": "Decimal" } }
  },
  {
    "type": "Calculated",
    "calculation": {
      "operation": "Round",
      "places": 2,
      "inputs": [
        { "operation": "Multiply", "inputs": [{ "column": "quantity" }, { "column": "unit_price" }] }
      ]
    },
    "map": { "value": { "column": "line_total", "dataType": "Decimal" } }
  }
]
quantity unit_price line_total
5 95.77 478.85
4 43.68 174.72
9 5.87 52.83
2 54.9 109.8
6 88.98 533.88
7 76.35 534.45

Reading other columns

A calculation may read only a column produced by a field declared before it in the same table's fields array. Fields are generated in declaration order, so at the point a calculation is evaluated, a field declared after it still contains its value from the previous row. Reading such a column would produce values offset by one row, so the request is rejected during validation. The error identifies the columns available at that position.

[
  {
    "type": "Calculated",
    "calculation": { "operation": "Upper", "inputs": [{ "column": "status" }] },
    "map": { "value": { "column": "status_upper", "dataType": "VarChar" } }
  },
  {
    "type": "String",
    "mode": "Lookup",
    "lookupValues": [{ "value": "Open" }, { "value": "Closed" }],
    "map": { "value": { "column": "status", "dataType": "VarChar" } }
  }
]

Rejected: status is declared after the calculation that reads it.

Declaring the fields a calculation depends on earlier in the array resolves this:

[
  {
    "type": "String",
    "mode": "Lookup",
    "lookupValues": [{ "value": "Open" }, { "value": "Closed" }],
    "map": { "value": { "column": "status", "dataType": "VarChar" } }
  },
  {
    "type": "Calculated",
    "calculation": { "operation": "Upper", "inputs": [{ "column": "status" }] },
    "map": { "value": { "column": "status_upper", "dataType": "VarChar" } }
  }
]
status status_upper
Open OPEN
Open OPEN
Closed CLOSED
Open OPEN
Open OPEN
Closed CLOSED

A calculation may read a column produced by any earlier field type, not only another Calculated field, and a Calculated field may in turn be read by a later one, which allows derivations to be chained across successive fields. A later field reads the result of a Calculated field by the column name in its map.

A column read by a calculation need not appear in the output. See Hidden columns.

Null handling

Except where an operation's own description states otherwise, a node with a null input evaluates to null, and that null propagates through each enclosing operation. A single null input therefore produces a null result for that row rather than an error.

{
  "operation": "Add",
  "inputs": [{ "column": "base_fee" }, { "column": "surcharge" }]
}

If surcharge is null for a row, the result is null for that row regardless of the value of base_fee.

The following operations handle a null input themselves and do not return null because of it:

  • Coalesce returns the first input that is not null.
  • Concat with skipNulls set omits a null input from the join instead of returning null for the whole result.
  • Template writes a null value as empty text.
  • RunningTotal leaves the total unchanged for a null input.
  • If and Case treat a null condition as not true. If returns else, and Case moves on to the next arm.
  • And returns true only when every input is true, and Or returns true when any input is true. A null input counts as not true, so And with a null input returns false, and Or returns false unless another input is true.
  • The IsNull and IsNotNull comparisons evaluate null inputs directly.
  • The Equals, NotEquals, In, and NotIn comparisons treat null as a value. Null equals null and does not equal anything else, so Equals of two nulls is true and NotEquals of null and 1 is true.
  • Map matches a null input against a mapping whose from is null, and returns default when there is none.

Not of a null returns null. Every other operation returns null for a null input.

A value that an operation cannot read as the type it needs is treated the same way as a null. Text in a column that is not a number returns null from Add, and text that is not an ISO 8601 date returns null from DateAdd. A literal is different: it is part of the request, so a literal an operation cannot read is rejected when the request is validated, with the path of the literal and the type it needs.

The result's data type

The value a calculation produces is converted to the dataType declared on map.value before it is written, using the same conversion Cast applies. Arithmetic operations return the numeric type of their inputs, so declaring the destination column as Decimal, Double, or Integer is normally sufficient and no explicit Cast is required at the end of the tree. Cast is required only where a value must change type partway through a calculation, before a subsequent operation is applied to it. See Types.

A calculation whose result can never be written to its column is rejected before any row is generated. Each operation produces one kind of value, and each kind can be written only to some column types:

The calculation produces It can be written to
A number Byte, Short, Integer, Long, Decimal, Float, Double, Boolean, Interval (as seconds), or a text type
True or false Boolean, a number type, or a text type
A date or time Date, Time, DateTime, Timestamp, or a text type
An interval Interval, or a text type
A geometry Geometry, Geography, or a text type
Text Any column type

DateAdd mapped to an Integer column is rejected, for example. Text can contain a number or a date, so a text result can be mapped to any column, and so can a value read straight from a column. If, Case, Map, Coalesce, Min and Max produce the kind of their branches when every branch produces the same kind. Use Cast to change a result's kind.

A result that is the right kind but does not fit its column in a particular row is written as null for that row. A whole number outside the range of an Integer, Short, Byte, or Long column is one example. Numbers written to a text column are written in full, without an exponent.

Arithmetic

Operation Inputs Properties Result
Add 2+ Sum of every input.
Subtract 2+ First input less every input after it.
Multiply 2+ Product of every input.
Divide 2+ First input divided by every input after it. Division by zero returns null rather than raising an error.
Modulo 2 Remainder of the first input divided by the second. Modulo by zero returns null rather than raising an error.
Power 2 First input raised to the power of the second. A result that is not a real number, such as a negative number raised to a fractional power, returns null.
Negate 1 The input with its sign reversed.
Abs 1 The absolute value of the input.
Round 1 places (default 0) The input rounded to places decimal places, half away from zero. A negative places rounds to the left of the decimal point: -2 rounds 1234.5678 to 1200.
Floor 1 The largest whole number no greater than the input.
Ceiling 1 The smallest whole number no less than the input.
Truncate 1 The input with its fractional part removed, toward zero.
Min 2+ The smallest of its inputs, compared across the row rather than down the table.
Max 2+ The largest of its inputs, compared across the row rather than down the table.
Clamp 1 low, high (at least one required) The input constrained to the range low to high. A low greater than high is rejected.
Sqrt 1 Square root of the input. A negative input returns null.
Log 1 logBase (default: natural log) Logarithm of the input, in logBase where set. An input at or below zero, or a logBase at or below zero or equal to one, returns null.
Exp 1 e raised to the power of the input. A result too large to represent returns null.

Add, Subtract, Multiply, and Divide retain exact arithmetic when every input is already exact, meaning a Decimal column or an integer literal. Ten Decimal values of 0.1 sum to exactly 1.0 rather than 0.9999999999999999. If any input is a Double or a Float column, or a literal with a fractional part such as 0.2, the operation is evaluated in floating point. Declare monetary inputs as Decimal to retain exact results. Mixing Decimal and Double inputs in the same operation is permitted, but the result is no longer exact.

Quantity and unit price multiplied and rounded to two decimal places:

[
  {
    "type": "Number",
    "mode": "Whole",
    "min": 1,
    "max": 12,
    "map": { "value": { "column": "quantity", "dataType": "Integer" } }
  },
  {
    "type": "Number",
    "mode": "Real",
    "min": 5,
    "max": 250,
    "scale": 2,
    "map": { "value": { "column": "unit_price", "dataType": "Decimal" } }
  },
  {
    "type": "Calculated",
    "calculation": {
      "operation": "Round",
      "places": 2,
      "inputs": [
        { "operation": "Multiply", "inputs": [{ "column": "quantity" }, { "column": "unit_price" }] }
      ]
    },
    "map": { "value": { "column": "line_total", "dataType": "Decimal" } }
  }
]
quantity unit_price line_total
9 56.57 509.13
2 159.94 319.88
9 133.05 1197.45
10 118.85 1188.5
8 73.03 584.24
10 157.45 1574.5

A sensor reading constrained to a fixed range, with its absolute value in a separate column:

[
  {
    "type": "Number",
    "mode": "Real",
    "min": -20,
    "max": 120,
    "scale": 1,
    "seed": 4,
    "map": { "value": { "column": "sensor_reading", "dataType": "Decimal" } }
  },
  {
    "type": "Calculated",
    "calculation": { "operation": "Clamp", "low": 0, "high": 100, "inputs": [{ "column": "sensor_reading" }] },
    "map": { "value": { "column": "clamped_reading", "dataType": "Decimal" } }
  },
  {
    "type": "Calculated",
    "calculation": { "operation": "Abs", "inputs": [{ "column": "sensor_reading" }] },
    "map": { "value": { "column": "magnitude", "dataType": "Decimal" } }
  }
]
sensor_reading clamped_reading magnitude
95.5 95.5 95.5
-8.9 0 8.9
76.7 76.7 76.7
100.8 100 100.8
77.3 77.3 77.3
51.3 51.3 51.3

Readings below 0 are raised to 0, and readings above 100 are lowered to 100. magnitude is each reading without its sign.

Text

Operation Inputs Properties Result
Template 0+ template (required) template with each placeholder substituted. See Template.
Concat 2+ separator (default: none), skipNulls (default false) Every input joined by separator.
Upper 1 The input in upper case.
Lower 1 The input in lower case.
TitleCase 1 The input with the first letter of each word capitalized and the rest in lower case. See TitleCase.
Trim 1 characters (default: white space) The input with characters removed from each end.
PadLeft 1 length (required), padCharacter (default: space) The input padded on the left to length.
PadRight 1 length (required), padCharacter (default: space) The input padded on the right to length.
Substring 1 start (required), length (default: to the end) length characters from start, counting from zero. Returns characters up to the end of the input where it is shorter than start plus length. start cannot be negative.
Left 1 length (required) The first length characters.
Right 1 length (required) The last length characters.
Replace 1 find (required), replaceWith (default: none, which removes find), regex (default false) Every occurrence of find replaced by replaceWith. regex set to true interprets find as a regular expression.
Length 1 The number of characters in the input, as a number.
Slug 1 separator (default -) The input reduced to a lower-case token. Accented letters are reduced to their base letter, and each run of characters that is not a letter or a digit is replaced by separator. See Slug.
Hash 1+ algorithm (default Sha256), separator (default: none) Every input joined by separator, then hashed and returned as lower-case hexadecimal. See Hash.

TitleCase

TitleCase capitalizes the first letter of each word and writes the rest in lower case. A hyphen starts a new word, so jean-pierre becomes Jean-Pierre. A letter after an apostrophe that follows a single letter is also capitalized, so o'neil becomes O'Neil, while don't becomes Don't.

Where the input contains lower-case letters, a word written entirely in capitals is treated as an abbreviation and kept as it is, so USB-C cable becomes USB-C Cable. Input with no lower-case letters at all is converted word by word, so JOHN O'NEIL becomes John O'Neil.

Template

Template substitutes values from the current row into a string, which avoids composing text from Concat and a series of conversions. {columnName} inserts that column's value. {0}, {1}, and so on insert the entry at that position in inputs, which applies where the inserted value is the result of another operation rather than a column:

[
  {
    "type": "Person",
    "countries": [{ "country": "us" }],
    "map": {
      "firstName": { "column": "first_name", "dataType": "VarChar" },
      "lastName": { "column": "last_name", "dataType": "VarChar" }
    }
  },
  {
    "type": "Calculated",
    "calculation": { "operation": "Template", "template": "{first_name} {last_name}" },
    "map": { "value": { "column": "full_name", "dataType": "VarChar" } }
  }
]
first_name last_name full_name
Raissa Plocek Raissa Plocek
Tiffannie Rosencutter Tiffannie Rosencutter
Fawaz Klaahsen Fawaz Klaahsen
Deijah Ringdahl Deijah Ringdahl
Janet Bialka Janet Bialka
Rave Ghai Rave Ghai

A literal brace is written as {{ or }}. An unclosed { is rejected during request validation. A {columnName} referencing a column that is not available at that position is rejected on the same basis as a { "column": ... } node. See Reading other columns.

Slug

Slug produces a value suitable for an email local part, a URL segment, or a file name, where the result must be predictable ASCII regardless of the script of the source value:

[
  {
    "type": "Person",
    "countries": [{ "country": "us" }],
    "map": {
      "firstName": { "column": "first_name", "dataType": "VarChar" },
      "lastName": { "column": "last_name", "dataType": "VarChar" }
    }
  },
  {
    "type": "Calculated",
    "calculation": {
      "operation": "Template",
      "template": "{0}.{1}@example.com",
      "inputs": [
        { "operation": "Slug", "inputs": [{ "column": "first_name" }] },
        { "operation": "Slug", "inputs": [{ "column": "last_name" }] }
      ]
    },
    "map": { "value": { "column": "email", "dataType": "VarChar" } }
  }
]
first_name last_name email
Emannuel Jacomet emannuel.jacomet@example.com
Hiwot Tolerico hiwot.tolerico@example.com
Demetri Akre demetri.akre@example.com
Orhan Dullen orhan.dullen@example.com
Bindiya Haeffner bindiya.haeffner@example.com
Mariesa Voskoboynik mariesa.voskoboynik@example.com

Accented Latin letters are reduced to their base letter, so é becomes e. Letters that are not formed from an ASCII base letter, such as ø or ł, are mapped individually to the nearest ASCII letter, so a value containing one of them still produces a complete token. A value written entirely in a script with no Latin form, such as Japanese or Greek, yields no ASCII characters, and the result is null for that row.

Hash

Hash produces a stable digest of its inputs, suitable for a pseudonymized key that must be reproducible for the same inputs:

[
  {
    "type": "Person",
    "countries": [{ "country": "us" }],
    "map": {
      "lastName": { "column": "last_name", "dataType": "VarChar" },
      "dob": { "column": "date_of_birth", "dataType": "Date" }
    }
  },
  {
    "type": "Calculated",
    "calculation": {
      "operation": "Hash",
      "algorithm": "Sha256",
      "separator": "|",
      "inputs": [{ "column": "last_name" }, { "column": "date_of_birth" }]
    },
    "map": { "value": { "column": "customer_key", "dataType": "VarChar" } }
  }
]
last_name date_of_birth customer_key
Swazey 1941-12-29 a20a840db0b13d81133784604af17e4bd1005c287df9f27a86cda1e4f3fcce3b
Kazandjian 1972-02-28 8593381bf6b74bfbc0f0a33d0371df66a7721907ddb9e87ee63af55818a075dc
Domville 1952-06-10 cc83ef246c569d742936177494280fa584dd0214bdfb6858476193c052d56295
Sekulski 1970-12-18 7d7973dc7477ee4e81b685a41cf83cc679b33c0e67552838903eae1f21070598
Santomauro 1957-05-11 145596a8287158bfee3fc620b30842cdf4c8aad2cf7a66a231d9d4229b7f7d00
Toyoda 1973-02-13 9a6dfd2b147b935243cd4833d7b9c7e9486d4ea4d57ceb7a76d26cbd140ec48a

algorithm is one of Sha256 (64 hexadecimal characters), Sha512 (128), Sha1 (40), or Md5 (32). None of these algorithms is suitable for password storage, which requires a salted, purpose-built algorithm rather than a general-purpose digest.

Trimming, casing, and slicing

Text operations compose in the same way as any others. The following normalizes a source value and derives a product code prefix and a sequence number from it:

[
  {
    "type": "String",
    "mode": "Lookup",
    "lookupValues": [{ "value": "  wireless mouse  " }, { "value": "usb-c cable" }, { "value": "laptop stand " }],
    "map": { "value": { "column": "raw_name", "dataType": "VarChar", "hidden": true } }
  },
  {
    "type": "Calculated",
    "calculation": {
      "operation": "TitleCase",
      "inputs": [{ "operation": "Trim", "inputs": [{ "column": "raw_name" }] }]
    },
    "map": { "value": { "column": "display_name", "dataType": "VarChar" } }
  },
  {
    "type": "Calculated",
    "calculation": {
      "operation": "Left",
      "length": 3,
      "inputs": [{ "operation": "Upper", "inputs": [{ "operation": "Trim", "inputs": [{ "column": "raw_name" }] }] }]
    },
    "map": { "value": { "column": "sku_prefix", "dataType": "VarChar" } }
  },
  {
    "type": "Calculated",
    "calculation": {
      "operation": "PadLeft",
      "length": 8,
      "padCharacter": "0",
      "inputs": [{ "operation": "RowNumber", "startAt": 1 }]
    },
    "map": { "value": { "column": "sku_number", "dataType": "VarChar" } }
  }
]

raw_name contains unnormalized source text such as " wireless mouse " and "usb-c cable". It is hidden, so only the values derived from it appear in the output.

display_name sku_prefix sku_number
Usb-C Cable USB 00000001
Wireless Mouse WIR 00000002
Usb-C Cable USB 00000003
Laptop Stand LAP 00000004
Laptop Stand LAP 00000005
Wireless Mouse WIR 00000006

PadLeft accepts any node as input, not only text. RowNumber is therefore converted to text and padded directly, with no intermediate Cast.

Concat with null inputs

Concat with skipNulls set omits a null input from the join instead of returning null for the whole result:

[
  {
    "type": "Number",
    "mode": "Whole",
    "min": 100,
    "max": 9999,
    "map": { "value": { "column": "street_number", "dataType": "Integer", "hidden": true } }
  },
  {
    "type": "String",
    "mode": "Lookup",
    "lookupValues": [{ "value": "Elm Way" }, { "value": "Oak Ave" }, { "value": "Main St" }],
    "map": { "value": { "column": "street_name", "dataType": "VarChar", "hidden": true } }
  },
  {
    "type": "String",
    "mode": "Lookup",
    "lookupValues": [{ "value": "Unit 12" }, { "value": "Suite 4" }, { "value": "Apt 7" }],
    "nullPercentageMin": 50,
    "nullPercentageMax": 50,
    "map": { "value": { "column": "unit", "dataType": "VarChar", "hidden": true } }
  },
  {
    "type": "Calculated",
    "calculation": {
      "operation": "Concat",
      "separator": ", ",
      "skipNulls": true,
      "inputs": [
        { "operation": "Template", "template": "{street_number} {street_name}" },
        { "column": "unit" }
      ]
    },
    "map": { "value": { "column": "address_line_1", "dataType": "VarChar" } }
  }
]

unit is null on about half the rows, representing an optional suite or apartment number.

address_line_1
7618 Oak Ave
9426 Main St
2947 Main St
302 Oak Ave
2578 Main St
3782 Main St, Apt 7

Without skipNulls, each row with a null unit would produce a null address_line_1.

Dates and times

Each operation in this section accepts Date, DateTime, Timestamp, or Time values and returns a result of the same type. Adding a month to a Date returns a Date, and adding an hour to a DateTime returns a DateTime. A literal string such as "2026-01-01" is interpreted in the same way as a column of the corresponding type.

Text is read as a date or time only when it is written in ISO 8601 form: 2026-03-15 for a date, 2026-03-15T09:30:00 or 2026-03-15 09:30:00 for a date and time, with Z or an offset such as +02:00 for a timestamp, and 09:30:00 for a time. A regional form such as 03/15/2026 is not read, because the same text means different dates in different countries. A text column in another form returns null, and a literal in another form is rejected.

Operation Inputs Properties Result
DateAdd 2 unit (omit to add an Interval instead) The first input advanced by the second. The second is a number of unit, or an Interval value where unit is not set.
DateSubtract 2 unit (omit to subtract an Interval instead) The first input moved back by the second, on the same basis as DateAdd.
DateDiff 2 unit (required) Complete unit from the first input to the second, as a number. Negative where the second is earlier.
Age 2 Completed years from the first input to the second, counted by the calendar. See Age.
DatePart 1 part (required) The component of the input set by part, as a number. See DatePart for the full list of parts.
DateTruncate 1 unit (required) The input reduced to the start of its unit.
CombineDateTime 2 A Date and a Time combined into one DateTime, taking the date from the first input and the time from the second.
DateInterval 2 The elapsed time from the first input to the second, as an Interval.
FormatDate 1 pattern (required) The input written as text in pattern, a custom date and time format string.

unit, for DateAdd, DateSubtract, DateDiff, and DateTruncate, is one of Years, Quarters, Months, Weeks, Days, Hours, Minutes, Seconds, or Milliseconds. Years and Months are calendar units rather than fixed durations. Adding one month to January 31 returns the last day of February, and DateDiff in Months counts complete calendar months rather than treating each month as 30 days. DateDiff returns the largest number of months that DateAdd can add to the first input without passing the second, so January 31 to February 28 is one month.

Age

Age is provided separately from DateDiff in Years because it applies an additional rule: a birthday that has not yet occurred in the later year is not counted. A person born on June 15, 1996 is 29 years old on March 1, 2026, not 30. Age applies this rule directly, without requiring a Case expression built on DatePart:

[
  {
    "type": "Date",
    "mode": "Random",
    "min": "1955-01-01",
    "max": "2007-12-31",
    "map": { "value": { "column": "date_of_birth", "dataType": "Date" } }
  },
  {
    "type": "Calculated",
    "calculation": { "operation": "Age", "inputs": [{ "column": "date_of_birth" }, { "literal": "2026-01-01" }] },
    "map": { "value": { "column": "age", "dataType": "Integer" } }
  },
  {
    "type": "Calculated",
    "calculation": {
      "operation": "Case",
      "cases": [
        { "when": { "operation": "Compare", "comparison": "LessThan", "inputs": [{ "column": "age" }, { "literal": 25 }] }, "then": { "literal": "18-24" } },
        { "when": { "operation": "Compare", "comparison": "LessThan", "inputs": [{ "column": "age" }, { "literal": 45 }] }, "then": { "literal": "25-44" } }
      ],
      "default": { "literal": "45+" }
    },
    "map": { "value": { "column": "age_band", "dataType": "VarChar" } }
  }
]
date_of_birth age age_band
1993-09-24 32 25-44
2003-11-14 22 18-24
1972-07-26 53 45+
1969-02-14 56 45+
2004-02-03 21 18-24
1990-05-09 35 25-44

The second input is the reference date, and it has no default. No current date is available, because the same request always produces the same data. Supply the reference date as a literal or as another generated column.

DatePart

part is one of Year, Quarter (1-4), Month (1-12), Week (ISO week of the year), Day (of the month), DayOfYear, DayOfWeek (1 for Monday through 7 for Sunday), Hour, Minute, Second, or OffsetMinutes (the UTC offset, for a Timestamp).

Moving and truncating dates

A subscription renewal date one year after the start date, with the calendar month of the start date:

[
  {
    "type": "Date",
    "mode": "Random",
    "min": "2025-01-01",
    "max": "2026-06-30",
    "map": { "value": { "column": "started_on", "dataType": "Date" } }
  },
  {
    "type": "Calculated",
    "calculation": { "operation": "DateAdd", "unit": "Months", "inputs": [{ "column": "started_on" }, { "literal": 12 }] },
    "map": { "value": { "column": "renews_on", "dataType": "Date" } }
  },
  {
    "type": "Calculated",
    "calculation": {
      "operation": "FormatDate",
      "pattern": "MMMM yyyy",
      "inputs": [{ "operation": "DateTruncate", "unit": "Months", "inputs": [{ "column": "started_on" }] }]
    },
    "map": { "value": { "column": "billing_month", "dataType": "VarChar" } }
  }
]
started_on renews_on billing_month
2026-06-17 2027-06-17 June 2026
2026-06-01 2027-06-01 June 2026
2026-06-24 2027-06-24 June 2026
2025-10-14 2026-10-14 October 2025
2025-10-14 2026-10-14 October 2025
2025-06-10 2026-06-10 June 2025

Geometry

Geometry inputs are read from the well-known text in a Geometry column, which is the format Geospatial produces. A value that cannot be parsed as a geometry, such as an empty string or malformed text, is treated as a missing input and returns null, consistent with any other node whose input cannot be interpreted as the required type.

A measurement across the earth's surface needs real coordinates, longitude -180 to 180 and latitude -90 to 90. A geometry with any coordinate outside that range returns null from Distance, GeometryLength, Area and Buffer in surface units, and from Bearing, Midpoint and Geohash. Measurements in Degrees and SquareDegrees are flat and accept any coordinates.

Operation Inputs Properties Result
Distance 2 units (required) The shortest distance between the two geometries, in units. See Required units.
Centroid 1 The center of mass of the input, as a Point. Can be outside the input, for example for a crescent or a ring.
PointOnSurface 1 A point that always lies on the input, unlike Centroid. See PointOnSurface.
CoordinateX 1 The longitude of the input, or of its centroid where the input is not itself a point.
CoordinateY 1 The latitude of the input, or of its centroid where the input is not itself a point.
Bearing 2 The initial compass bearing from the first point to the second, in degrees from north. Both inputs must be points, and any other geometry returns null.
Midpoint 2 The point half way along the surface path between two points, as a Point. Both inputs must be points, and any other geometry returns null.
Area 1 areaUnits (required) The area the input encloses, in areaUnits.
GeometryLength 1 units (required) The length of a line, or the perimeter of a polygon, in units.
Envelope 1 The bounding box of the input, as a Polygon.
Buffer 1 units (required), distance (required) The input expanded by distance in units. See Buffer.
ToGeoJson 1 The input rendered as a GeoJSON geometry object rather than well-known text.
Geohash 1 precision (default 9) The position of the input encoded as a geohash of precision characters (1-12).
Intersects 2 Whether the two inputs share at least one point. Returns a boolean.
Within 2 Whether the first input lies entirely inside the second. Returns a boolean.

Required units

Distance, GeometryLength, Area, and Buffer each require an explicit units property and provide no default. Degrees and SquareDegrees measure in the coordinate system itself, where one degree of longitude corresponds to approximately 111 km at the equator and decreases toward the poles. All other units, meaning Meters, Kilometers, Miles, NauticalMiles, Feet, and Yards for distance and length, and SquareMeters, SquareKilometers, SquareMiles, Hectares, and Acres for area, measure across the surface of the earth. A distance in degrees cannot easily be distinguished from a distance in another unit, so the unit has no default:

{ "operation": "Distance", "inputs": [{ "column": "origin" }, { "column": "destination" }] }

Rejected: Distance has no units.

Surface measurement also handles the antimeridian correctly. On the equator, two points at longitude 179 and -179 are about 222 kilometers apart, because the surface path crosses the antimeridian between them. Measured in Degrees, the same two points are 358 apart, because a coordinate-system measurement takes the longitudes as they are written.

Distance and initial bearing between two generated points:

[
  {
    "type": "Geospatial",
    "geometryType": "Point",
    "boundingLocation": { "country": "gb", "region": "eng" },
    "map": { "value": { "column": "origin", "dataType": "Geometry", "hidden": true } }
  },
  {
    "type": "Geospatial",
    "geometryType": "Point",
    "boundingLocation": { "country": "gb" },
    "map": { "value": { "column": "destination", "dataType": "Geometry", "hidden": true } }
  },
  {
    "type": "Calculated",
    "calculation": {
      "operation": "Round",
      "places": 1,
      "inputs": [{ "operation": "Distance", "units": "Kilometers", "inputs": [{ "column": "origin" }, { "column": "destination" }] }]
    },
    "map": { "value": { "column": "distance_km", "dataType": "Double" } }
  },
  {
    "type": "Calculated",
    "calculation": {
      "operation": "Round",
      "places": 0,
      "inputs": [{ "operation": "Bearing", "inputs": [{ "column": "origin" }, { "column": "destination" }] }]
    },
    "map": { "value": { "column": "bearing_degrees", "dataType": "Double" } }
  }
]
distance_km bearing_degrees
328.2 309
672.6 340
735.2 333
583 336
86.3 214
225.2 300

Buffer

Buffer expands a geometry by a ground distance rather than by a fixed number of degrees. A degree of longitude corresponds to a different ground distance at each latitude, so expansion applied directly in degrees produces a distorted result away from the equator. Buffer compensates for this, so a 10 kilometer buffer encloses the same ground area at any latitude. A buffer that would reach past longitude -180 or 180, or latitude -90 or 90, is cut off at that edge.

A delivery catchment area around a generated store location, with the geohash of the store:

[
  {
    "type": "Geospatial",
    "geometryType": "Point",
    "boundingLocation": { "country": "us", "state": "ca" },
    "map": { "value": { "column": "location", "dataType": "Geometry", "hidden": true } }
  },
  {
    "type": "Calculated",
    "calculation": { "operation": "Buffer", "units": "Kilometers", "distance": 10, "inputs": [{ "column": "location" }] },
    "map": { "value": { "column": "catchment", "dataType": "Geometry", "hidden": true } }
  },
  {
    "type": "Calculated",
    "calculation": {
      "operation": "Round",
      "places": 0,
      "inputs": [{ "operation": "Area", "areaUnits": "SquareKilometers", "inputs": [{ "column": "catchment" }] }]
    },
    "map": { "value": { "column": "catchment_km2", "dataType": "Double" } }
  },
  {
    "type": "Calculated",
    "calculation": { "operation": "Geohash", "precision": 7, "inputs": [{ "column": "location" }] },
    "map": { "value": { "column": "geohash", "dataType": "VarChar" } }
  }
]
catchment_km2 geohash
312 9r0czvq
312 9q7hk92
312 9qdvxrj
312 9myx9e6
312 9q9k4n9
312 9r14k9h

catchment_km2 is the same on every row, because the buffer is measured on the surface at each latitude. A circle with a radius of 10 km has an area of about 314 km². The buffer is a polygon with straight sides, so its area is slightly less. A buffer measured in degrees would have a different area at each latitude.

PointOnSurface

Centroid returns the center of mass of a geometry, which is outside the geometry itself for concave geometries such as a horseshoe or a crescent. PointOnSurface always returns a point on the geometry, which is required where the result is used as a representative location rather than as a geometric center:

{
  "operation": "PointOnSurface",
  "inputs": [{ "column": "service_area" }]
}

Within and Intersects

Within and Intersects compare two geometries and return a boolean, so a table can record whether a generated point is inside a generated area as well as the distance between them:

[
  {
    "type": "Geospatial",
    "geometryType": "Point",
    "boundingLocation": { "country": "us", "city": "Los Angeles", "state": "ca" },
    "map": { "value": { "column": "warehouse", "dataType": "Geometry", "hidden": true } }
  },
  {
    "type": "Calculated",
    "calculation": { "operation": "Buffer", "units": "Kilometers", "distance": 20, "inputs": [{ "column": "warehouse" }] },
    "map": { "value": { "column": "service_area", "dataType": "Geometry", "hidden": true } }
  },
  {
    "type": "Geospatial",
    "geometryType": "Point",
    "boundingLocation": { "country": "us", "city": "Los Angeles", "state": "ca" },
    "map": { "value": { "column": "customer_location", "dataType": "Geometry", "hidden": true } }
  },
  {
    "type": "Calculated",
    "calculation": { "operation": "Within", "inputs": [{ "column": "customer_location" }, { "column": "service_area" }] },
    "map": { "value": { "column": "in_delivery_zone", "dataType": "Boolean" } }
  },
  {
    "type": "Calculated",
    "calculation": {
      "operation": "Round",
      "places": 1,
      "inputs": [{ "operation": "Distance", "units": "Kilometers", "inputs": [{ "column": "warehouse" }, { "column": "customer_location" }] }]
    },
    "map": { "value": { "column": "distance_km", "dataType": "Double" } }
  }
]
in_delivery_zone distance_km
true 4.9
true 13
true 10.6
false 30.3
true 11.7
false 30

warehouse and customer_location are generated independently, so some customers are outside the 20 km service area. in_delivery_zone is true on every row where distance_km is 20 or less.

Conditions

Operation Inputs Properties Result
If 0 when, then (required), else (default: null) then where when evaluates true, otherwise else.
Case 0 cases (required), default (default: null) The result of the first arm in cases whose when evaluates true, or default where none do.
Coalesce 2+ The first input that is not null.
Compare see Compare comparison (required) Whether the inputs meet the condition set by comparison. Returns a boolean.
And 2+ Whether every input is true.
Or 2+ Whether any input is true.
Not 1 The negation of the input.
Map 1 mappings (required), default (default: null) The input translated through mappings, returning default where no entry matches. See Map.

Compare

comparison is one of:

Comparison Inputs Tests
Equals 2 The inputs are equal. Text is compared ignoring case.
NotEquals 2 The inputs are not equal.
GreaterThan 2 The first input sorts after the second.
GreaterThanOrEqual 2 The first input sorts after the second, or equal to it.
LessThan 2 The first input sorts before the second.
LessThanOrEqual 2 The first input sorts before the second, or equal to it.
In 2+ The first input equals any of the inputs after it.
NotIn 2+ The first input equals none of the inputs after it.
Between 3 The first input falls between the second and the third, inclusive at both ends.
IsNull 1 The single input is null.
IsNotNull 1 The single input is not null.
Contains 2 The first input contains the second, ignoring case.
StartsWith 2 The first input begins with the second, ignoring case.
EndsWith 2 The first input ends with the second, ignoring case.
Matches 2 The first input matches the regular expression supplied as the second. A pattern written in the request that is not a valid regular expression is rejected. A pattern read from a column that is not valid returns null for that row.

The ordered comparisons, meaning GreaterThan, GreaterThanOrEqual, LessThan, LessThanOrEqual, and Between, compare numeric values numerically, date and time values chronologically, and all other values as text. 5 and "5" compare as equal, as do a Date column and a literal date string, so a Cast is rarely required before a comparison.

[
  {
    "type": "String",
    "mode": "Lookup",
    "lookupValues": [{ "value": "Open" }, { "value": "Pending" }, { "value": "Closed" }],
    "map": { "value": { "column": "status", "dataType": "VarChar" } }
  },
  {
    "type": "Calculated",
    "calculation": { "operation": "Compare", "comparison": "In", "inputs": [{ "column": "status" }, { "literal": "Open" }, { "literal": "Pending" }] },
    "map": { "value": { "column": "is_active", "dataType": "Boolean" } }
  },
  {
    "type": "Number",
    "mode": "Whole",
    "min": 1,
    "max": 10,
    "map": { "value": { "column": "priority", "dataType": "Integer" } }
  },
  {
    "type": "Calculated",
    "calculation": { "operation": "Compare", "comparison": "Between", "inputs": [{ "column": "priority" }, { "literal": 4 }, { "literal": 7 }] },
    "map": { "value": { "column": "is_medium_priority", "dataType": "Boolean" } }
  }
]
status is_active priority is_medium_priority
Closed false 2 false
Open true 9 false
Open true 5 true
Open true 2 false
Open true 1 false
Closed false 9 false

If and Case

An order routed for review above a threshold, with a loyalty tier derived from the same total:

[
  {
    "type": "Number",
    "mode": "Real",
    "min": 50,
    "max": 2500,
    "scale": 2,
    "seed": 2,
    "map": { "value": { "column": "total", "dataType": "Decimal" } }
  },
  {
    "type": "Calculated",
    "calculation": {
      "operation": "If",
      "when": { "operation": "Compare", "comparison": "GreaterThan", "inputs": [{ "column": "total" }, { "literal": 1000 }] },
      "then": { "literal": "Review" },
      "else": { "literal": "Auto" }
    },
    "map": { "value": { "column": "approval_path", "dataType": "VarChar" } }
  },
  {
    "type": "Calculated",
    "calculation": {
      "operation": "Case",
      "cases": [
        { "when": { "operation": "Compare", "comparison": "LessThan", "inputs": [{ "column": "total" }, { "literal": 200 }] }, "then": { "literal": "Bronze" } },
        { "when": { "operation": "Compare", "comparison": "LessThan", "inputs": [{ "column": "total" }, { "literal": 1000 }] }, "then": { "literal": "Silver" } }
      ],
      "default": { "literal": "Gold" }
    },
    "map": { "value": { "column": "tier", "dataType": "VarChar" } }
  }
]
total approval_path tier
485.15 Auto Silver
853.94 Auto Silver
639.77 Auto Silver
125.34 Auto Bronze
1051.63 Review Gold
666.94 Auto Silver

Case evaluates its arms in order and returns the result of the first arm whose when is true. default is returned for a total of 1000 or more.

And, Or, Not

Eligibility from two separate conditions:

[
  {
    "type": "Number",
    "mode": "Whole",
    "min": 16,
    "max": 75,
    "seed": 2,
    "map": { "value": { "column": "age", "dataType": "Integer" } }
  },
  {
    "type": "Boolean",
    "percentageTrue": 60,
    "seed": 1002,
    "map": { "value": { "column": "has_license", "dataType": "Boolean" } }
  },
  {
    "type": "Calculated",
    "calculation": {
      "operation": "And",
      "inputs": [
        { "operation": "Compare", "comparison": "GreaterThanOrEqual", "inputs": [{ "column": "age" }, { "literal": 18 }] },
        { "column": "has_license" }
      ]
    },
    "map": { "value": { "column": "eligible_to_drive", "dataType": "Boolean" } }
  }
]
age has_license eligible_to_drive
26 true true
35 false false
30 true true
17 true false
40 true true
31 true true

A boolean column may be supplied directly as an input, as has_license is here, without an enclosing Compare.

Coalesce

Coalesce returns the first input that is not null, which applies where a value has a preferred source and one or more fallbacks:

[
  {
    "type": "Name",
    "countries": [{ "country": "us" }],
    "part": "FirstName",
    "caseOutput": "Proper",
    "nullPercentageMin": 50,
    "nullPercentageMax": 50,
    "seed": 3,
    "map": { "value": { "column": "preferred_name", "dataType": "VarChar" } }
  },
  {
    "type": "Name",
    "countries": [{ "country": "us" }],
    "part": "FirstName",
    "caseOutput": "Proper",
    "seed": 1003,
    "map": { "value": { "column": "legal_first_name", "dataType": "VarChar" } }
  },
  {
    "type": "Calculated",
    "calculation": { "operation": "Coalesce", "inputs": [{ "column": "preferred_name" }, { "column": "legal_first_name" }] },
    "map": { "value": { "column": "display_name", "dataType": "VarChar" } }
  }
]

preferred_name is null on about half the rows. On those rows, display_name contains legal_first_name:

preferred_name legal_first_name display_name
Muang Tuere Muang
Tkeyah Tkeyah
Lynzy Aarron Lynzy
Yatziri Yatziri
Agusta Agusta
Johnathan Jyssica Johnathan

Map

Map provides a compact form of a Case in which every arm tests the same input for equality against a fixed value:

[
  {
    "type": "String",
    "mode": "Lookup",
    "lookupValues": [{ "value": "ca" }, { "value": "ny" }, { "value": "tx" }, { "value": "wa" }],
    "seed": 1,
    "map": { "value": { "column": "state", "dataType": "VarChar" } }
  },
  {
    "type": "Calculated",
    "calculation": {
      "operation": "Map",
      "mappings": [
        { "from": "ca", "to": { "literal": "West" } },
        { "from": "wa", "to": { "literal": "West" } },
        { "from": "ny", "to": { "literal": "East" } }
      ],
      "default": { "literal": "Other" },
      "inputs": [{ "column": "state" }]
    },
    "map": { "value": { "column": "region", "dataType": "VarChar" } }
  }
]
state region
ca West
ca West
ny East
wa West
tx Other
ny East

The equivalent Case would require one when and Compare pair per state in place of each mappings entry. from is matched using the same rules as Equals, which ignores case for text. A from of null matches a null input.

Position in the table

Operation Inputs Properties Result
RowNumber 0 startAt (default 1) The index of the current row, counting from startAt.
RunningTotal 1 The input added to the accumulated total of all preceding rows. 
[
  {
    "type": "Calculated",
    "calculation": { "operation": "RowNumber", "startAt": 1000 },
    "map": { "value": { "column": "line_number", "dataType": "Integer" } }
  },
  {
    "type": "Number",
    "mode": "Real",
    "min": 10,
    "max": 500,
    "scale": 2,
    "map": { "value": { "column": "amount", "dataType": "Decimal" } }
  },
  {
    "type": "Calculated",
    "calculation": { "operation": "RunningTotal", "inputs": [{ "column": "amount" }] },
    "map": { "value": { "column": "invoice_to_date", "dataType": "Decimal" } }
  }
]
line_number amount invoice_to_date
1000 325.92 325.92
1001 470.57 796.49
1002 176.15 972.64
1003 100.92 1073.56
1004 200.77 1274.33
1005 322.32 1596.65

RunningTotal accumulates as rows are generated, so invoice_to_date on any row is the sum of amount for that row and all preceding rows. Rows are generated in order, so the total is computed in a single forward pass. Exactness follows the same rule as the arithmetic operations. Accumulating Decimal values keeps the total exact. Once an inexact value is added, the total is floating point for every later row.

RunningTotal does not follow the rule described in Null handling. A null input leaves the accumulated total unchanged and returns that total, rather than returning null for the row, so a null contributes nothing to the total instead of invalidating every row after it.

RunningTotal accumulates over the rows generated so far. It is not a whole-table aggregate. No operation computes an aggregate over the complete table, such as a sum or an average applied back to every row, because that requires a second pass over generated data. RowNumber and RunningTotal both operate in a single forward pass, so every calculation remains a function of the current row and the rows already generated.

Types

Operation Inputs Properties Result
Cast 1 targetType (required) The input converted to targetType, one of the column data types listed in Data types.
ParseNumber 1 The input parsed as a number, returning null where it does not parse. 
[
  {
    "type": "String",
    "mode": "Lookup",
    "lookupValues": [{ "value": "42" }, { "value": "7" }, { "value": "153" }],
    "map": { "value": { "column": "raw_quantity", "dataType": "VarChar", "hidden": true } }
  },
  {
    "type": "Calculated",
    "calculation": { "operation": "Cast", "targetType": "Integer", "inputs": [{ "column": "raw_quantity" }] },
    "map": { "value": { "column": "quantity", "dataType": "Integer" } }
  },
  {
    "type": "Calculated",
    "calculation": { "operation": "Multiply", "inputs": [{ "column": "quantity" }, { "literal": 2 }] },
    "map": { "value": { "column": "doubled", "dataType": "Integer" } }
  }
]
quantity doubled
153 306
42 84
42 84
153 306
7 14
42 84

quantity is cast explicitly because Multiply requires a numeric input and raw_quantity is text. The final map.value conversion requires no corresponding Cast, as described in The result's data type.

ParseNumber performs the same conversion as Cast to a numeric type. It is provided separately for cases where the purpose of the calculation is to determine whether a text value parses as a number, returning null where it does not.

Examples

Each example below is an array of consecutive field objects, in the order they appear in a table's fields array within a full request. See Building a Request for how it fits into a full DatasetRequest.

An email address from a name

Template joins the two names, Slug reduces them to a lower-case token separated by a period, and Concat adds the domain.

[
  {
    "type": "Person",
    "countries": [{ "country": "us" }],
    "map": {
      "firstName": { "column": "first_name", "dataType": "VarChar" },
      "lastName": { "column": "last_name", "dataType": "VarChar" }
    }
  },
  {
    "type": "Calculated",
    "calculation": {
      "operation": "Concat",
      "inputs": [
        {
          "operation": "Slug",
          "separator": ".",
          "inputs": [{ "operation": "Template", "template": "{first_name} {last_name}" }]
        },
        { "literal": "@example.com" }
      ]
    },
    "map": { "value": { "column": "email", "dataType": "VarChar" } }
  }
]
first_name last_name email
Naida Krajicek naida.krajicek@example.com
Renaye Elo renaye.elo@example.com
Felicha Declercq felicha.declercq@example.com
Kaedan Braschler kaedan.braschler@example.com
Gwendy Cuautle gwendy.cuautle@example.com
Xian Forche xian.forche@example.com

Net, tax and gross for an order line

Each calculation reads the columns before it, including the results of earlier calculations.

[
  {
    "type": "Number",
    "mode": "Whole",
    "min": 1,
    "max": 10,
    "map": { "value": { "column": "quantity", "dataType": "Integer" } }
  },
  {
    "type": "Number",
    "mode": "Real",
    "min": 1,
    "max": 100,
    "scale": 2,
    "precision": 5,
    "map": { "value": { "column": "unit_price", "dataType": "Decimal" } }
  },
  {
    "type": "Calculated",
    "calculation": {
      "operation": "Round",
      "places": 2,
      "inputs": [{ "operation": "Multiply", "inputs": [{ "column": "quantity" }, { "column": "unit_price" }] }]
    },
    "map": { "value": { "column": "net", "dataType": "Decimal" } }
  },
  {
    "type": "Calculated",
    "calculation": {
      "operation": "Round",
      "places": 2,
      "inputs": [{ "operation": "Multiply", "inputs": [{ "column": "net" }, { "literal": 0.2 }] }]
    },
    "map": { "value": { "column": "tax", "dataType": "Decimal" } }
  },
  {
    "type": "Calculated",
    "calculation": { "operation": "Add", "inputs": [{ "column": "net" }, { "column": "tax" }] },
    "map": { "value": { "column": "gross", "dataType": "Decimal" } }
  }
]
quantity unit_price net tax gross
3 57.19 171.57 34.31 205.88
8 67.75 542 108.4 650.4
5 23.63 118.15 23.63 141.78
9 81.37 732.33 146.47 878.8
8 78.91 631.28 126.26 757.54
5 3.18 15.9 3.18 19.08

A due date and a billing period from an invoice date

[
  {
    "type": "Date",
    "mode": "Random",
    "min": "2026-01-01",
    "max": "2026-06-30",
    "map": { "value": { "column": "invoice_date", "dataType": "Date" } }
  },
  {
    "type": "Calculated",
    "calculation": { "operation": "DateAdd", "unit": "Days", "inputs": [{ "column": "invoice_date" }, { "literal": 30 }] },
    "map": { "value": { "column": "due_date", "dataType": "Date" } }
  },
  {
    "type": "Calculated",
    "calculation": { "operation": "FormatDate", "pattern": "MMMM yyyy", "inputs": [{ "column": "invoice_date" }] },
    "map": { "value": { "column": "billing_period", "dataType": "VarChar" } }
  }
]
invoice_date due_date billing_period
2026-04-29 2026-05-29 April 2026
2026-03-28 2026-04-27 March 2026
2026-03-05 2026-04-04 March 2026
2026-06-19 2026-07-19 June 2026
2026-03-30 2026-04-29 March 2026
2026-05-08 2026-06-07 May 2026

Account codes and a running balance

RowNumber padded to six digits gives each row a code, and RunningTotal accumulates the amounts down the table.

[
  {
    "type": "Calculated",
    "calculation": {
      "operation": "Template",
      "template": "CUST-{0}",
      "inputs": [{ "operation": "PadLeft", "length": 6, "padCharacter": "0", "inputs": [{ "operation": "RowNumber" }] }]
    },
    "map": { "value": { "column": "customer_code", "dataType": "VarChar" } }
  },
  {
    "type": "Number",
    "mode": "Real",
    "min": -500,
    "max": 1000,
    "includeNegative": true,
    "scale": 2,
    "precision": 7,
    "map": { "value": { "column": "amount", "dataType": "Decimal" } }
  },
  {
    "type": "Calculated",
    "calculation": { "operation": "RunningTotal", "inputs": [{ "column": "amount" }] },
    "map": { "value": { "column": "balance", "dataType": "Decimal" } }
  }
]
customer_code amount balance
CUST-000001 756.85 756.85
CUST-000002 -355.92 400.93
CUST-000003 67.5 468.43
CUST-000004 747.94 1216.37
CUST-000005 272.64 1489.01
CUST-000006 494.64 1983.65

Distance from a fixed depot

A literal point and a generated column can be inputs to the same operation.

[
  {
    "type": "Geospatial",
    "geometryType": "Point",
    "centerX": -0.1276,
    "centerY": 51.5072,
    "radius": 20,
    "radiusUnitOfMeasure": "Kilometer",
    "map": { "value": { "column": "drop_off", "dataType": "Geometry" } }
  },
  {
    "type": "Calculated",
    "calculation": {
      "operation": "Round",
      "places": 1,
      "inputs": [
        {
          "operation": "Distance",
          "units": "Kilometers",
          "inputs": [{ "literal": "POINT(-0.1276 51.5072)" }, { "column": "drop_off" }]
        }
      ]
    },
    "map": { "value": { "column": "km_from_depot", "dataType": "Double" } }
  }
]
drop_off km_from_depot
POINT(-0.158876 51.437287) 8.1
POINT(-0.060277 51.674456) 19.2
POINT(-0.283584 51.392677) 16.7
POINT(0.077301 51.47339) 14.7
POINT(0.151676 51.498924) 19.4
POINT(-0.028164 51.666397) 19

A default for a missing value

Coalesce returns the first input that is not null, so an empty preferred_contact falls back to Email.

[
  {
    "type": "String",
    "mode": "Lookup",
    "lookupValues": [{ "value": "Mobile" }, { "value": "Email" }, { "value": "Post" }],
    "nullPercentageMin": 30,
    "nullPercentageMax": 30,
    "map": { "value": { "column": "preferred_contact", "dataType": "VarChar" } }
  },
  {
    "type": "Calculated",
    "calculation": { "operation": "Coalesce", "inputs": [{ "column": "preferred_contact" }, { "literal": "Email" }] },
    "map": { "value": { "column": "contact_method", "dataType": "VarChar" } }
  }
]
preferred_contact contact_method
Post Post
Post Post
Email
Post Post
Post Post
Email