This section describes various auxiliary functions. These functions should be used after data extraction functions.
Get Title
Adds a Title’s column to the resulting table.
Format:
GetTitle()
Parameters:
no parameters
Data type returned:
table
Description:
The presence of the GetTitle() function in the expression indicates that when retrieving data, you should get the Item’s Title of the Catalogs.
The name of the resulting field is constructed as the name of the source field with the addition of the _title postfix. The Title for the person field will be person_title.
If the data source is intended for filling in the operation, then, as a result, the Titles of the Catalogs are not needed, the identifiers will be enough for the system.
Get Titles only when users will work with the resulting data, for example, the data will be used when creating the report. On large volumes of data, obtaining Titles can take a considerable amount of time. You can save time in cases where Titles are not needed.
Example:
Inventory | GroupBy (store, product, quantity, amount) | GetTitle () – returns the table with columns: store, store_title, product, product_title, quantity, amount.
Value
Returns a value from the specified column and row in a table. If the line number is not specified, the value is extracted from the first line. Line numbering starts with 1.
Format:
Value( name, <rowNumber>)
Parameters:
- name – table column name
- <rowNumber> – table row number, default 1
Data type returned:
value
Example:
Money | BudgetItem ($h.BudgetItem) | GroupBy( BudgetItem, amount) | Value ( amount )
CreateTable
Creates a table with the specified columns.
Format:
CreateTable( columnName1 columnType1,…, columnNameN columnTypeN)
Parameters:
columnName1,…, columnNameN – names of a new columns
columnType1, … , columnTypeN – new columns 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:
You can add values to the table using the AddRow function. ( see more AddRow function)
Example:
CreateTable (Item integer, Cost number) – creates a table with two columns Item and Cost.
AddRow
Adds a row to the table whit values.
Format:
AddRow( value1,…, valueN)
Parameters:
value1,…, valueN – the number of arguments must match the number of columns in the table. Values are placed in columns according to their order. If you do not need to add values to some columns, then the values should be left empty, but the number of arguments should match the number of columns in the table.
Data type returned:
table
Example:
CreateTable (Item integer, cost number) | AddRow ($h.item, $h.costs)
CloneRow
Create copies of the rows as indicated in the column <cloneCount> of the input table
Format:
CloneRow ( <cloneCount> )
Parameters:
<cloneCount> – the name of the column that indicates the required number of rows
Data type returned:
table
Example:
CreateTable (Item integer, repetition number, quantity number ) | AddRow (1, 4, 1 ) | CloneRow ( repetition )
AddColumn
Adds new columns at the end of the table with the pre-configured parameters specified below
Format:
AddColumn( name1, columnType1, defaultValue1, …, nameN, columnTypeN, defaultValueN )
Parameters:
name1, …, nameN are the names of the columns to add.
columnType1, …, columnTypeN is the type of column to add. The following values are valid:
- string – string
- number – number
- integer – an integer (must be used to set the identifiers of directories)
- boolean – boolean
- datetime – date and time
- defaultValue1, …, defaultValueN – the default value.
Data type returned:
table
Example:
Inventory | Product ( pipe_5 ) | GroupBy ( Product ) | AddColumn (NewPrice, number, 50 ) | – set NewPrice for the product – pipe_5.
DeleteColumn
Deletes columns ( name1,…, nameN ) from the resulting Table.
Format:
DeleteColumn( name1,…, nameN)
Parameters:
name1, …, nameN are the names of the columns to delete.
Data type returned:
table
Example:
Inventory | GroupBy ( Product, Quantity, Amount ) | Compute ( Price, Amount / Quantity ) | DeleteColumn( Quantity, Amount) – returns the resulting table with columns Product and Price.
Distinct
Selects only unique rows from the source table. Uniqueness is determined by the key fields specified as arguments . The list of table columns does not change. The resulting table will contain all the columns in the source table.
Format:
Distinct( name1,…, nameN )
Parameters:
name1,…, nameN are the names of the columns
Data type returned:
table
Example:
Money | Period ( ThisYear ) | Select( customer ) | Distinct( customer ) – returns list of customers.
List
Gets a column from the table as a list of values. The values in the list are not duplicated.
Format:
List ( name, <rowNumber> )
Parameters:
- name – table column name
- <rowNumber> – table row number, default 1
Data type returned:
list of values
Description:
Gets a value from the specified column and row in a table.
If the line number is not specified, the value is extracted from the first line. Line numbering starts at 1.
Example:
Booking | Select ( Product ) | List (Product) – returns list of products id.
MakeTree
It creates a tree of groups and elements.
Format:
MakeTree( <idColumn>, <parentIdColumn>, <isGroupColumn>, <recource1>,…,<recourceN> )
Parameters:
- idColumn – the name of the column containing the item identifiers
- <parentIdColumn> – the name of the column containing the identifiers of the parents of the elements
- <isGroupColumn> – the name of the column containing the flag that the row contains a group
- <recource1>, … ,<recourceN> – names of numeric columns for which totals by groups will be calculated.
Data type returned:
table
Description:
The function adds the following standard columns to the resulting table:
- _level – level in the tree
- _order_in_tree – the general order of the line in the tree.
It is used to build reports with analytics on budget items.
Example:
Money | Period(&datestart, &datefinish) | GroupBy( BudgetItem, amount);
Catalog.BudgetItem | LeftJoin( $result_1, id, title, presentation, parent, isGroup, result_1.amount as amount, result_1.budgetitem = id ) | MakeTree( id, parent, isgroup, amount )
The first expression receives turnover in the Register Money for the period.
The second expression gets all the items of the Catalog.BudgetItems and performs a left join with the previously obtained turnover table.
Then, the MakeTree function is applied to the resulting table, which forms a structure for creating a Cache flow report.
See more used MakeTree function Fixed Table Report.
ExpandByPeriod
Creates a table of dates between the set start and end with the set periodicity. Parameters are set in the source table.
Format:
ExpandByPeriod( periodColumnName, dateStart, dateFinish, periodicity)
Parameters:
- periodColumnName – the name of the column in which the date will be added
- dateStart – period start date
- dateFinish – period end date
- periodicity – Periodicity. It can take the values: day, month, year.
Data type returned:
table
Description:
It can be used both in data sources and in record sources in operations.
Example:
This example creates a table for a car reservation schedule.
CreateTable( car integer days number) | AddRow( 1, 1 ) | ExpandByPeriod( date, $h.date_start, $h.date_finish, day )
If $h.date_start = “2020-01-01” and $h.date_finish = “2020-01-03” it will return table below (see Fig 1.).
| car | days | date |
| 1 | 1 | 2020-01-01 |
| 1 | 1 | 2020-01-02 |
| 1 | 1 | 2020-01-03 |
Coalesce
Computes the arguments in order and returns the current value of the first expression that was not initially calculated as NULL.
Format:
Coalesce ( result_column, value_1, … , value_n )
Parameters:
- result_column – result not NULL column;
- value_1, … , value_n – the name of the column;
Data type returned:
table
Description:
Often used to replace null values with 0.
Example:
This example will return a table in which there will be no null values in the column quantity.
Select (order, customer, product, quantity as quantity_1) | AddColumn(quantity_0, number, 0) | Coalesce (quantity, quantity_1, quantity_0 ) | DeleteColumn ( quantity_1, quantity_0 )