Logic
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).
This page is an orientation. The complete language reference, with every function and option, is generated from the product: Kex reference ↗.
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#
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:
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 |
Static data can be written inline:
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 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#
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 |
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 for console, API and MCP scripts.
from planin the query does not move the target;use plandoes. - Selected columns. In a write, the value column is named
$result, and each dimension of the target must be assigned, either implicitly or withDim = ….
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:
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 afterwhere,checkorfrom 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
$calcidin 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}`
- text:
- Arithmetic and text:
^ * / + -,&concatenation,??coalesce. - Comparison:
==,!=(alias<>),<,>,<=,>=. - Pattern matching:
=~and!~with*and?. - Membership:
in {…}andnot in {…}, also on multi-valued properties and on tuples(a, b) in {(…)}. - Logic:
&&,||,not. - Null semantics.
x == nullis true for null.x == yis 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 ↗ 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#
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.
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.
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
withinthere 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.withintakes 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.
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.
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 nowrite. Values must be strings.@("Label")before avarorscalaris 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.
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.
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).
scalar Total = 0
for X in sum(Amount), Account {
set Total += sum(X)
}
setnever 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, andset V += 1000applies to every row. - A scalar is evaluated when it is first used. A scalar defined from another variable, but first read after a
setof 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:
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#
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, with write-specific pitfalls under 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:
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 and the Kex Lite reference ↗.
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 |
REST /api/v1/kex/script/* |
Yes | Yes, in the default plan | Separate validate, read and write endpoints, see APIs |
| MCP | execute_read_script |
execute_write_script |
validate_script first, see AI agents |
| Dataset expressions | Kex Lite | Via writeback | See Datasets |
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.