VendorGate

Expressions

Expressions let you compute output values with formulas. They are available in expression-mode mappings and inside expression transform steps.

What you can use

  • Arithmetic operators: +, -, *, /, //, %, **
  • Comparisons: ==, !=, <, >, <=, >=
  • Boolean operators: and, or (short-circuiting)
  • Input column references by name or by alias and field, such as orders.quantity
  • Helper functions

Helper functions

String

  • upper(value), lower(value), trim(value): case and whitespace.
  • left(value, n), right(value, n): first or last n characters.
  • pad_left(value, width, char), pad_right(value, width, char): pad to width.
  • replace(value, old, new): string replacement.
  • concat(...): concatenate arguments, skipping null.
  • contains(value, substring), startswith(value, prefix), endswith(value, suffix): substring checks.
  • titlecase(value), normalize_whitespace(value): text normalization.
  • len(value): string length.
  • split_index(value, separator, index): split and return indexed part.

Null and blank

  • coalesce(value, fallback, ...), ifnull(value, fallback, ...): first non-null value.
  • is_blank(value), is_null(value): null/blank checks.
  • nullif(value, sentinel): return null when value == sentinel.

Type coercion

  • to_int(value), to_float(value), to_str(value), to_bool(value), is_numeric(value).

Numeric

  • round(value, decimals), abs(value), min(...), max(...), clamp(value, lo, hi).

Lookup, regex, and JSON

  • lookup(dataset_alias, match_column, return_column, value): look up a value from a reference dataset or input alias.
  • regex_extract(value, pattern, group): extract a regex capture group.
  • json_extract(value, path): extract a value from JSON using dot notation.

Vendor normalization

  • normalize_phone(value), normalize_email(value), normalize_zip5(value), normalize_ein(value).
  • parse_money(value): parse $1,234.56 or accounting-style negatives.

Conditional

  • if_else(cond, true_val, false_val) / iif(cond, true_val, false_val): evaluate only the selected branch.
  • case(cond1, val1, cond2, val2, ..., default?): first true condition wins.
  • is_in(value, ...): membership check.

Utility

  • similarity(a, b): difflib ratio (0..1).
  • hash(...): SHA-256 of joined arguments.
  • today(format): current UTC date string.
  • date_diff_days(a, b, format): day difference between two date strings.

Examples

Calculate a line total:

expression: "qty * unit_price"

Provide a fallback for missing values:

expression: "ifnull(qty, 0) * ifnull(price, 0)"

Normalize a code before looking it up:

expression: 'lookup("brand_map", "vendor_brand_code", "internal_brand_name", upper(brand_code))'

Null handling

coalesce and ifnull treat null as missing. Empty strings are treated as explicit values, so you can supply string fallbacks such as ifnull(country, "").

Security

Expressions run in a restricted sandbox. Only safe arithmetic, comparisons, and the documented helper functions are allowed. Imports, attribute access, and comprehensions are not permitted.