We are using cookies to give you the best experience on our site. Cookies are files stored in your browser and are used by most websites to help personalise your web experience.
By continuing to use our website without changing the settings, you are agreeing to our use of cookies.
COEL formulas
COEL formula represents a mathematical function which computes one output value for input values. One COEL expression determines how one output value is computed while it is the task of the engine to loop through all inputs and store the outputs.
Compare how COEL expressions are different from spreadsheet formulas. For example, the following formula defines a value in the cell C1
as a sum of values in A1
and B1
:
= A1 + B1
Note that the value of only one cell is defined and will be computed. Each cell has two coordinates.
In COEL, we define columns rather than cells and an analogous formula computing column C
from columns A
and B
could be written as follows:
= A + B
Note that now the arguments have only one coordinate: column name. This means that the system will evaluate this formula for all rows (inputs) by producing all outputs rather than a single value. This is why we call it column formula and not cell formula.
Contents 
Calculated columns
Calculated columns return a primitive value which is computed from input values read from this same table. Assume that table LineItems
has two columns price
and quantity
. We can compute a new column amount
as follows:
[amount] = [price] * [quantity]
Each value of the amount
column is equal to the product of values in the other two columns from same row of the table.
Link columns
Link columns are thought of as a substitute for joins.
A COEL expression can return an element of nonprimitive table. Note however that it is not possible to compute a nonprimitive output value using arithmetic operations. To return a nonprimitive value, it is necessary to define a tuple from the output set. In other words, if an expression returns a tuple then this means that it returns a value representing the corresponding (matching) element from the output set.
Assume that we want to define a new column order
to the table LineItems
which returns a purchase order this item belongs to from the table Orders
. This can be done as follows:
[order] = TUPLE( supp=[supplierId], no=[orderNo] )
Here supp
and no
are column of the output table Orders
while supplierId
and orderNo
are columns of the input table LineItems
.
Importantly, once the link column order
has been defined, it can be used as a normal column in other expressions which returns an order for each line item.
Accumulation columns
Accumulation columns are thought of as a substitute for groupby operation.
An accumulation column is able to aggregate multiple values during evaluation of its formula. These multiple values are produced while looping through some other table which is referred to as a fact table.
For example, we could add a new column total
to the table Orders
by summing up the prices of all line items belong to this order:
[total] = ACCU( [LineItems], [order], [amount], SUM )
The accumulation function takes four parameters:

LineItems
is a fact table 
order
is grouping column which specifies an order this fact record belongs to. It is a link column which can be an arbitrary COEL expression. 
amount
is a measure column the output values of which are being accumulated. It is an existing column or an arbitrary calculated column. 
SUM
is an aggregation function which specifies how the multiple values returned by the measure column have to be accumulated.