# Kex language

> A structured introduction to Kex, Konstrukt's query and scripting language — statements and variables, clause order, implicit resolution, joins, write variants, dimension operators, windowing, Kex Lite — with links to the auto-generated full reference.

Kex is Konstrukt's language for reading, filtering, joining, aggregating and writing data. The same language is used in calculations, actions, flows, the Kex console, the REST endpoint `/api/v1/kex` and the MCP tools. You state *what* you want in terms of value types, dimensions and properties, and the engine works out the joins and grouping. Kex compiles to set-based SQL, one table per value type (see [Architecture](architecture.html#the-calculation-engine)).

This page is an orientation. The complete language reference, with every function and option, is generated from the product: **[Kex reference ↗](https://acme.konstrukt.app/api/documentation/kex)**.

!!! agent "Authoritative sources for agents"
    The MCP server exposes the reference as resources: `KexOverview`, `KexDocumentation` (full reference), `KexGuidelines` (rules for generated scripts), `KexKnowledge` (pitfalls) and `KexScriptExamples`, under `konstrukt://resources/kex/`. Load the overview first and the full reference when needed. Always run `validate_script` before `execute_write_script`, and never retry a failed write script automatically.

## A first query

```kex
Amount where Month.Year == "2026"
```

This returns `Amount` for 2026 with all of its dimensions projected automatically. Aggregation to fewer dimensions is expressed by naming the dimensions you want:

```kex
Total = sum(Amount), Department where Month.Year == "2026"
```

Keywords are lowercase and case-sensitive. Whitespace is not significant.

## Statements and variables

A script is one or more statements.

| Statement | Purpose |
|---|---|
| `var X = <query>` | A table variable: dimensions plus one or more value columns. Later columns are addressed by alias. |
| `scalar X = <query>` | A single value (the first row); extra columns as `X.Alias` |
| `dim X = <query or list>` | A custom dimension from unique first-column values, or `dim X = X from plan 10` to import one |
| `evaluate [Name] = <expr>` | A named result set; bare expressions are also evaluated |
| `write … <- <query>` | Write to a value type, see [Write variants](#write-variants) |

Static data can be written inline:

```kex
var Rates = (Currency, Rate) { ("EUR", 11.2), ("USD", 10.4) }
dim Months = {202601, 202602, 202603}
dim Weeks = range('2026-01-05', '2026-03-30', days(7))
```

`range(start, stop, step?)` generates numeric, date and time, or single-character sequences. The start and stop values are inclusive.

A variable can shadow a value type of the same name. Dimensions cannot be shadowed. Start variable names with a capital letter to avoid clashing with future keywords.

## Clause order

Clauses must appear in this order:

| # | Clause | Examples |
|---|---|---|
| 1 | Select expressions | `Total = sum(Amount), Department` · `only` suppresses automatic dimension projection |
| 2 | Include options | `main X`, `include X`, `include optional X`, `include all X`, `include cross X`, `on D.Prop`, `default D.Prop to C`, `expand D.Prop`, `exclude calculated` |
| 3 | Filter | `where …` |
| 4 | Grouping and order | `group by`, `having`, `order by` |
| 5 | Data source | `from plan <id>`, `from reference <id or "name">`, `from history <datetime> [values only]` |
| 6 | Output options | `limit n`, `check duplication`, `check includes`, `check dim values`, `apply dim formatting`, `apply dim filtering` |

## Implicit resolution

Most scripts need few include options, because Kex infers them:

- **Dimensions** of the main table are projected automatically.
- **The main table** is chosen heuristically: value tables first, then dimensions, then the table sharing the most dimensions, then the first one named.
- **Joins** are resolved from shared dimensions and bound properties.
- **Grouping and ordering** follow from the selected dimensions.

`check includes` turns off much of this inference and requires explicit includes. Guided calculations use it.

The calculation editor's [Diagnostics](calculations.html#diagnostics) shows every resolution step applied to a query.

### Identifier expansion

| Form | Meaning |
|---|---|
| `X.*` | All columns of `X` |
| `X.**` | Including internal columns (`$calcid`, `$edittime`, …), useful for troubleshooting |
| `Amount.*.*` | Multi-step expansion through properties |
| `Region.*.**$name*` | Expansion filtered by name pattern |

## Joins

| Include option | Join | Guided editor term |
|---|---|---|
| `main X` | Driving table | Main |
| `include X` | Inner | *Inre* |
| `include optional X` | Left outer | *Vänster* |
| `include all X` | Full outer | *Fullständig yttre* |
| `include cross X` | Cross product (applied before `all`) | — |
| `… on Dim.Prop` | Join on a bound property | *Bunden egenskap* |

`default Dim.Prop to <constant>` supplies a value where a property is missing.

## Write variants

```kex
write [[insert|upsert] suggestion | insert | update | replacement] [to] Target [reference <id|"name">] <- <query>
```

| Variant | Outside a calculation | Inside a calculation |
|---|---|---|
| *(no keyword)* | Upsert: update matching rows, insert new ones | Replace the calculation's own rows in the target |
| `insert` | Only rows without data | Same, within the calculation's rows |
| `update` | Only existing rows; no inserts or deletes | Same |
| `replacement` | Replace target contents with the result, deleting non-matching rows. **Calculated rows are not deleted.** | Same as no keyword |
| `suggestion` | Not allowed | Write suggested values the user may overwrite, see [Suggestions](calculations.html#suggestions) |
| `insert suggestion` | Not allowed | Suggest only where no data exists |
| `upsert suggestion` | Not allowed | Update and add suggestions |
| `… reference <id|"name">` | Write reference data instead of plan data | Same |

Rules that follow from this:

- **Row ownership.** Inside a calculation, writes touch only rows carrying that calculation's `$calcid`. The first write to a target clears the calculation's earlier rows; later writes to the same target in the same run upsert.
- **Write target.** The target is resolved in the plan the script runs in: the calculation's plan, or the [default plan](concepts.html#plan) for console, API and MCP scripts. `from plan` in the query does not move the target; [`use plan`](#use-plan) does.
- **Selected columns.** In a write, the value column is named `$result`, and each dimension of the target must be assigned, either implicitly or with `Dim = …`.

```kex
write to SalaryCost <-
  $result = sum(Salary) * (1 + EmployerRate),
  Employee, Month
having $result != 0
```

### Excluding calculated rows

`exclude calculated`, in the include-option position, leaves calculated rows out of the query, so that only user-entered and imported rows are read. It is often used together with `write replacement` in actions, which rewrite user data without touching calculated data:

```kex
write replacement to Amount <- Amount.* exclude calculated
  where not (Department in @Department && Month in @Month)
```

The documented equivalent is `expand X.$calcid where X.$calcid == null`. Suggested values carry no calculation ID, so `exclude calculated` still returns them.

- The option can stand anywhere among the include options (`main`, `include`, `default`) and directly after the select list, but not after `where`, `check` or `from plan`.
- It applies to every table in the query, not only to `main`.
- It works in plain reads too: `count() only main Amount exclude calculated`.
- It cannot be combined with a reference to `$calcid` in the same query: *The 'exclude calculated' option cannot be used when the '$calcid' column is referenced elsewhere in the query.*
- There is no `exclude suggestions`.

## Dimension operators on variables

| Operator | Effect |
|---|---|
| `dimadd Dim = <expr>` | Add an existing plan dimension to a variable that lacks it, enabling joins across value types that share no dimension. Filters apply to the added dimension. |
| `dimset Dim = <expr>` | Override a dimension already on the variable. The expression affects projection and grouping; **filtering still uses the original dimension**. |

Non-existent dimension codes are filtered lazily, only when the dimension is referenced. `check dim values`, `apply dim filtering` or re-assignment force the check.

## Expressions

- **Constants:**
    - text: `"abc"`
    - numbers
    - dates and times: `'2026-05-17'`, `'12:34:22'`, `'2026-05-17 12:00'`
    - interpolated strings: `` `Total {x}` ``
- **Arithmetic and text:** `^ * / + -`, `&` concatenation, `??` coalesce.
- **Comparison:** `==`, `!=` (alias `<>`), `<`, `>`, `<=`, `>=`.
- **Pattern matching:** `=~` and `!~` with `*` and `?`.
- **Membership:** `in {…}` and `not in {…}`, also on multi-valued properties and on tuples `(a, b) in {(…)}`.
- **Logic:** `&&`, `||`, `not`.
- **Null semantics.** `x == null` is true for null. `x == y` is false when both values are null.
- **Multi-valued text.** Values are separated by `;`. Escape a literal `;` as `\;` and a backslash as `\\`.

### Functions

Kex has about 95 functions. See the [reference ↗](https://acme.konstrukt.app/api/documentation/kex) for signatures.

| Category | Examples |
|---|---|
| Conditional | `if`, `case`, `switch` |
| Conversion | `convert(expr, type)` (null on failure), casts `int()`, `string()` (throw on failure) |
| Date and time | `dateadd`, `datediff`, `now`, `today`, `startofmonth`, `endofyear`, `year`, `month`, `week`, `yyyymm`, `days()`, `months()` |
| Aggregate | `sum`, `min`, `max`, `average`, `count`, `distinctcount`, `product`, `concatenatex` |
| Window | `first`, `last`, `lag`, `lead`, and any aggregate with window bounds |
| Text | `concatenate`, `left`, `right`, `substring`, `length`, `upper`, `lower`, `substitute`, `padstart` |
| Math | `mod`, `abs`, `floor`, `ceiling`, `round`, `log`, `exp`, `rangemax`, `rangemin`, `coalesce` |
| Special | `autodim`, `typeof`, `aggregationof` |

### Windowing

```kex
Cumulative = sum(Amount) over all previous rows ordered by (Month) partitioned by (Account)
```

The general form is `FUNC [bound] [ordered by (…)] [partitioned by (…)]`. The bounds are:

- `for previous row`, `for next row`, `for row offset N`;
- `over N previous rows`, `over all previous rows`, `over N next rows`, `over all next rows`;
- `over rows between … and …`;
- `over all rows`.

A bound includes the current row. A trailing `-` excludes it. Windowed functions do not aggregate; nest them in an aggregate to group.

## Control flow and messages

Scripts can branch and stop. This is mostly used in actions, to validate input before writing.

```kex
scalar [Code] = makecode(@Employee.Name) within (Employee)
if any (Employee == [Code]) {
  fatal `Employee '{[Code]}' already exists.`
}
write insert to Employee <- Employee = [Code], $name = @Employee.Name
```

| Form | Condition |
|---|---|
| `if (cond) { … }` | A condition on scalars only. A value type or dimension gives *Top-level conditions may only reference scalar values.* |
| `if any (cond) { … }` | True when at least one row satisfies the condition. The condition can read value types, dimensions, properties and variables, and accepts `where` and `from plan`: `if any (Amount > 200 && Month == "202602" from plan 12)`. `main` and `include` are not allowed inside it. |
| `if not any (cond) { … }` | True when no row satisfies the condition |
| `else { … }`, `else if [any] (…) { … }` | Alternative branches. Blocks can be nested. |

A block may contain `if`, `for`, `for row`, `scalar`, `dim`, `var`, `set`, `fatal` and annotations. **Queries and `write` statements are not allowed inside a block**; branch on the condition, then write after the block, or stop with `fatal`.

### fatal

``fatal `message` `` stops the script, and the message is returned as the error: *There was an error executing the Kex script. Employee 'DemoPerson' already exists.* `fatal` is only allowed inside a block, and the message must be a back-quoted string.

`{…}` in the message inserts the value of an expression over scalars: `{[Code]}`, `{Code}`, `{[Code] & "-x"}` or `{1+2}`. A value type or dimension gives *Fatal messages may only reference scalar values.* There are no other message levels; `warning`, `info` and `error` are unknown identifiers.

!!! agent "For agents"
    If a result set is evaluated before the `fatal`, `execute_read_script` returns a generic tool error and the `fatal` message is lost. Test guards in scripts that evaluate nothing before them.

### makecode

`makecode(text) within (Dim)` turns a name into a dimension code that is unique in `Dim`. It is used in actions that create dimension values.

| Input | Result |
|---|---|
| `"Account 1"` | `Account1` |
| `"lead and trail"` | `LeadAndTrail` |
| `"åäö ÅÄÖ Émile"` | `aaoAAOEmile` |
| `"a.b-c_d/e & f! (g)"` | `a.b-c_d/e&F!(g)` |
| `"Demo Person"`, when `DemoPerson` … `DemoPerson_4` exist | `DemoPerson_5` |
| `""` in a dimension with numeric codes | the highest numeric code + 1 |

- Whitespace and the characters ``; , ' ? # * ~ { } |`` are removed, and the character after them is upper-cased. Otherwise the case is kept.
- `å ä ö é è ü` are transliterated to ASCII. Other letters are kept as they are.
- The code is at most 100 characters.
- Uniqueness is checked against existing **codes**, ignoring case, not against names. On a clash, `_2`, `_3` … is appended.
- Without `within` there is no uniqueness check, and an empty text gives a GUID.
- The argument must be a constant string or an `@` input: *Only constant string expressions are supported by the 'makecode' keyword.* `within` takes one dimension.

`within (Dim)` looks the dimension up in the plan the script runs in. For a console, API or MCP script in another plan, start with [`use plan`](#use-plan).

### Action inputs

In an action script, `@Identifier` refers to an input of the action, character for character, including dots: `@Employee`, `@Employee.Name`, `@Amount.Value`. The value behaves as a constant, so it can be used in `makecode`. Inputs cannot be declared in the script (`set @X = …` is a syntax error). Outside an action, an input gives *Unknown identifier: '@Employee.Name'.* See [Actions](actions.html#variables).

### Editor annotations

Guided calculations carry metadata for the calculation editor:

- `@@(model: "Formula", version: "1.0")` must be the first statement. `"PreCalcFormula"` marks a precalculation, which ends with a result query and has no `write`. Values must be strings.
- `@("Label")` before a `var` or `scalar` is the display label of a source in the editor.

Neither affects execution. Removing `@@(…)` and saving turns a guided calculation into a script calculation permanently; see [Calculations](calculations.html).

### use plan

`use plan <id>` or `use plan "<name>"` makes that plan the context for every statement after it: reads without `from plan`, write targets and `makecode … within`. An explicit `from plan` still overrides it for its query. The last `use plan` wins, and statements before it are not affected; a scalar declared earlier keeps its plan.

```kex
use plan 12
write to Forecast <- sum(Actuals) * 1.05, Account, Month
```

`use plan` takes a literal, not a variable, and is not allowed inside blocks. An unknown plan gives *No such plan: 9999*.

### set

`set` changes a variable that is already declared with `var`, `scalar` or `dim`. The operators are `=`, `+=`, `-=`, `*=`, `/=`, `&=` (text), `^=` and `??=` (assign if null).

```kex
scalar Total = 0
for X in sum(Amount), Account {
  set Total += sum(X)
}
```

- `set` never declares: *The 'set' keyword cannot be used to assign undeclared variable 'X'.*
- The columns must stay the same: *The 'set' keyword cannot be used to change the columns of variable 'X'.*
- On a table variable, `set V = …` replaces all its rows, and `set V += 1000` applies to every row.
- A scalar is evaluated when it is first used. A scalar defined from another variable, but first read after a `set` of that variable, gets the new value.

### for and for row

`for X in <query> { … }` runs the block once per row of the query; `X` is a one-row table, so read it with aggregates such as `sum(X)` or `min(X.Account)`. `for row in <query> { … }` is the same loop with the variable named `row`. The query can have `where` and `from plan`, and loops can be nested. An empty query runs zero times.

The body follows the block rules: `var`, `scalar`, `dim`, `set`, `if`, `for` and `fatal`, but no queries and no `write`. Variables declared in the body are not visible after the loop; use `set` on an outer variable to carry results out.

### spread

`spread (Source [evenly | proportionally] over Target)` is an expression that distributes a coarser source down to the grain of `Target`. Without a mode it spreads **proportionally** to the target's values:

```kex
var Budget = sum(Qty), Account          // 20 for account A1
spread (Budget evenly over Amount)      // 10 and 10 over A1's two rows
spread (Budget over Amount)             // 5 and 15, weighted by Amount 100 and 300
```

The result has the target's dimensions and can be used in `var`, in a query or as the source of a `write`. Only numeric data can be spread; the source cannot have the same dimensions as the target, or dimensions the target lacks (*Pre-aggregate the source table to exclude any extraneous dimensions.*).

## Pitfalls

!!! agent "Known pitfalls"
    There are known pitfalls when writing Kex — windowing over un-aggregated value types, duplicated rows from custom grouping, cross-plan reads and write targets. They are documented for agents in the [Agent guide](agent-pitfalls.html#kex), with write-specific pitfalls under [Writes](agent-pitfalls.html#writes).

## Kex Lite

App dataset expressions use **Kex Lite**, a restricted dialect evaluated per slice of a dataset. The platform generates a context for each expression, for example:

```kex
dim Department = $slice.Department limit 1
var Amount = pivot($slice, "Amount")
```

The expression then refers to the dataset's value types and dimensions by name. Writeback expressions use `$input` for the entered value. See [Datasets](datasets.html#expressions) and the **[Kex Lite reference ↗](https://acme.konstrukt.app/api/documentation/kex-lite)**.

## Where Kex runs

| Context | Reads | Writes | Notes |
|---|---|---|---|
| Calculation | Yes | Yes (own rows) | Triggered on source change |
| Action | Yes | Yes | Run by a user, with `@Variable` inputs |
| Flow | Yes | Yes | Run on demand with parameters |
| Kex console | Yes | Yes | Admin tool, see [Administration](administration.html) |
| REST `/api/v1/kex/script/*` | Yes | Yes, in the default plan | Separate validate, read and write endpoints, see [APIs](apis.html#kex-endpoints) |
| MCP | `execute_read_script` | `execute_write_script` | `validate_script` first, see [AI agents](ai-agents.html) |
| Dataset expressions | Kex Lite | Via writeback | See [Datasets](datasets.html) |

!!! note "Not in the generated reference"
    `exclude calculated`, `makecode`, `if` blocks, `fatal`, `@` inputs and the editor annotations are missing from the auto-generated Kex reference. They are described on this page.

## Related

- [Calculations](calculations.html)
- [Actions](actions.html)
- [References and history](references-history.html)
- [Reference documentation](reference-docs.html)
