Quick links:
The functions presented in this section provide additional processing for already extracted data. These functions should be used after sections with functions that extract data.
Compute
The function allows you to calculate the simplest expressions and add the calculation results to new columns of the table.
Format:
Compute( newField1, expression1, newField2, expression2, … , newFieldN, expressionN)
Parameters:
newField1, … , newFieldN – names of result’s columns
expression1, … , expressionN – a simple expressions for calculation
Data type returned:
table
Description:
If aliases were set for fields in previously used expression functions, then you should use them in expressions.
Example:
Inventory | GroupBy ( Product, Quantity, Amount ) | Compute ( Price, Amount / Quantity ) – the column Price calculated by the formula Amount / Quantity will be added to the resulting table.
Eval
Computes the expressions specified by the text and contained in the column of the expression table. Expressions are calculated in the context of a table row. Use table column names as arguments to an expression.
Format:
Eval ( newColumn, expressionColumn, <newColumnType> )
Parameters:
newColumn – name of result’s column
expressionColumn – column name with the text of expression
<newColumnType> – new column data type:
- string – string
- number – number
- integer – an integer (must be used to set the identifiers of directories)
- boolean – boolean
- datetime – date and time
Data type returned:
table
Description:
The difference between Compute and Eval.
The Compute function is used for calculation when writing a data source and is the same for the entire column of the table. The Eval function allows you to calculate expressions that were previously saved in text form, for example, in a Register or Catalog. And these expressions may vary in the rows of the table.
Example:
Specification | Select ( Product, Formula, A, B ) | Eval ( Result, Formula, number ) – calculates the Area according to the formula given in the column Formula.
| Product | Formula | A | B | Result |
| item 1 | ( A+B ) * 2 | 1 | 2 | 6 |
| item 2 | A + B | 3 | 3 | 6 |
| item 3 | ( A + B ) / 2 | 5 | 5 | 5 |
Having
Filters the result of a data source using a simple logical expression.
Format:
Having( expression )
Parameters:
expression – comparison expression
Data type returned:
table
Description:
If aliases were previously set for fields, then you must use them in expressions. Used only after functions that retrieve values.
Using Filter functions before Data extraction is more efficient because we initially include only the necessary data in the processed table. If possible, preference should be given to using filter functions before Data extraction.
You can use the following comparison operators in an expression:
Comparison operators
| Name | Operator | Examples |
| Equally | = | Product = Catalog.Products.Car |
| Not equal | <> | Product <> service |
| Is more | > | Amount > 1000 |
| More or equal | >= | Quantity <= 20 |
| Is less | < | Price < 10 |
| Less or equal | <= | not budgetitem = 18 |
Logical operators
| Name | Operator | Examples |
| Logical “And” | And | Amount > 1000 And Amount < 2000 |
| Logical “OR” | Or | Product = service or Product = goods |
| Logical “NOT” | Not | Not check = true |
Example:
Money | Period (ThisMonth) | GroupBy (CompanyAccount, BudgetItem, Amount) | Having ( amount > 1500 ) – returns records in which the Amount field value is more than 1500.
OrderBy
Sorts by the specified fields. If the sort direction ( asc | desc ) is not specified, ascending sorting is performed.
Format:
OrderBy( name1 <asc, desc>, name2 <asc, desc>,…,nameN <asc, desc>)
Parameters:
name1 … nameN – the names of the columns
Data type returned:
table
Example:
Inventory | GroupBy ( Product, Quantity, Amount ) | OrderBy( Amount desc, Product ) –
Top
Selects the first n rows from the source table.
Format:
Top( n )
Parameters:
n – number of rows selected
Description:
If you need to select the last rows of the table, you should change the direction of sorting with OrderBy, and select the first rows.
Data type returned:
table
Example:
Sales | GroupBy (Product, Amount) | OrderBy (Amount desc) | Top (3) – returns three best-selling products.
Sales | GroupBy (Product, Amount) | OrderBy (Amount) | Top (3) – returns three least selling items. From the previous expression, this expression differs only in the direction of sorting.