Insert with calculation rule

IMPORTANT: This operation can only be displayed and used with the licence for the ESG module.

Function¶

The insert with calculation rule operation allows you to perform complex calculations, working on values by source dataset row, instead of by column. In particular, given a source dataset and a set of calculation rules, this operation allows you to insert the rows generated by the calculations within the target dataset.

For this type of operation, specific attributes can be specified. See Operations page for a description.

Given an operation, for every statement of a script, the system runs the defined calculation, considering the logical dimensions of every operand, both input and output. As for Custom MD Calculations, the system can combine several uniform, non-uniform or separate operands or operands without logical dimensions, grouping the information present on the data and applying the rules of the MD syntax.

Logical dimensions and scripts are defined in the calculation rule scripts page (see Calculation rules page).

For a list of the functions that can be run using calculation rules, see Functionalities supported by the calculation rules.

Note: The set of scripts to be run for the operation are determined by the system when DTP is launched.

All scripts that have the label specified in the operation are taken into account, and among them are taken those that have the active flag enabled; scripts that have the execution condition satisfied are chosen. If start and/or end dates are specified, they must be valid in the range of the period scenario. Start and end dates are only checked when a scenario/period submission process is launched.

Example 1: output and input operands with logical dimensions on inverted columns

Source Dataset¶

Operand Field	Column_1	Column_2	Unit_Measurement	Value Field
OPERAND_INPUT	North	Woman	m	50
OPERAND_INPUT	North	Men	Km	40
OPERAND_INPUT	Center	Woman	m	70
OPERAND_INPUT	Center	Men		30
OPERAND_INPUT	South	Woman	Km	55
OPERAND_INPUT	South	Men	m	35
OPERAND_INPUT	East	Rather not say	dm	9
OPERAND_INPUT	East	Rather not say	dm	10
OPERAND_INPUT	East	Rather not say	dm	11

Logical dimensions table¶

Operand Code Script	Logical Dimension	Dataset Field
OPERAND_INPUT	GEOGRAPHIC_AREA	Column_1
OPERAND_INPUT	GENDER	Column_2
OPERAND_OUTPUT	GEOGRAPHIC_AREA	Column_2
OPERAND_OUTPUT	GENDER	Column_1

Script¶

TGKML 1.0 [TARGET].[OPERAND_OUTPUT] := [SOURCE].[OPERAND_INPUT];

Target Dataset¶

Operand Field	Column_1	Column_2	Value Field
OPERAND_OUTPUT	Woman	North	50
OPERAND_OUTPUT	Men	North	40
OPERAND_OUTPUT	Woman	Center	70
OPERAND_OUTPUT	Men	Center	30
OPERAND_OUTPUT	Woman	South	55
OPERAND_OUTPUT	Men	South	35
OPERAND_OUTPUT	Rather not say	East	9
OPERAND_OUTPUT	Rather not say	East	10
OPERAND_OUTPUT	Rather not say	East	11

Example 2: output and input operands with non-uniform logical dimensions

Source Dataset¶

Operand Field	Column_1	Column_2	Unit_Measurement	Value Field
OPERAND_INPUT	North	Woman	m	50
OPERAND_INPUT	North	Men	Km	40
OPERAND_INPUT	Center	Woman	m	70
OPERAND_INPUT	Center	Men		30
OPERAND_INPUT	South	Woman	Km	55
OPERAND_INPUT	South	Men	m	35
OPERAND_INPUT	East	Rather not say	dm	9
OPERAND_INPUT	East	Rather not say	dm	10
OPERAND_INPUT	East	Rather not say	dm	11

Logical dimensions table¶

Operand Code Script	Logical dimension	Dataset Field
OPERAND_INPUT	GEOGRAPHIC_AREA	Column_1
OPERAND_INPUT	GENDER	Column_2
OPERAND_OUTPUT	GENDER	Column_1

Script¶

TGKML 1.0 [TARGET].[OPERAND_OUTPUT] := [SOURCE].[OPERAND_INPUT];

Calcolo Operand_output¶

The system copies the values to the output and then groups the rows with the same value by the logical dimension 'GENDER'.

Target Dataset¶

Operand Field	Column_1	Value Field
OPERAND_OUTPUT	Woman	175 = (50+70+55)
OPERAND_OUTPUT	Men	105 = (40+30+35)
OPERAND_OUTPUT	Rather not say	30 = (9+10+11)

Audit information¶

The following information is shown in the Audit window:

within the operation, every script associated with the label inserted in the configuration step
within every script, each statement contained in the script
for every statement, the details of the query run in read mode and write mode

Use Reference dataset¶

In the operation setup step, it is possible to set a reference dataset to be used to run an implicit conversion of the values of the source dataset read in every statement of the rule script.

This dataset must have:

a single field with Attribute reference type, which must contain the conversion factor to be multiplied to the original data, by its conditions.
a single field without reference type with the same name as the field used as the operand field in the source and target datasets in order to implicitly associate the output operand with the corresponding conversion factor.
one or more Condition reference type fields to be able to identify the correct conversion factor

As is the case when using reference datasets in other DTP operations, the condition fields are automatically related by code to the source dataset fields and "special" conditions can be used by inserting values with the '%' character in the reference dataset.

For example, A% means everything that starts with A. Thus, instead of equality between the values, the condition defined through the SQL like operator (%) will be used. A null value, however, indicates the valid relationship for any value.

IMPORTANT: This operation not only uses database resources but also works in memory, so in terms of performance, the number of rows of the source dataset being processed must be taken into account. In the case of conversion, the number of rows of the reference dataset could also have a major impact on performance (from the tests performed, the memory consumption of the application server is around 500 MB for a case in which the source dataset has a few million rows and produces an output of around 100,000 rows).

For the previous examples, same source dataset if

Example 1

Source Dataset¶

Operand_Field	Column_1	Column_2	Unit_Measurement	Value Field
OPERAND_INPUT	North	Woman	m	50
OPERAND_INPUT	North	Men	Km	40
OPERAND_INPUT	Center	Woman	m	70
OPERAND_INPUT	Center	Men		30
OPERAND_INPUT	South	Woman	Km	55
OPERAND_INPUT	South	Men	m	35
OPERAND_INPUT	East	Rather not say	dm	9
OPERAND_INPUT	East	Rather not say	dm	10
OPERAND_INPUT	East	Rather not say	dm	11

Script¶

TGKML 1.0 [TARGET].[OPERAND_OUTPUT] := [SOURCE].[OPERAND_INPUT];

Reference dataset¶

Operand_Field	Unit_Measurement	Conversion_Factor	Filter_Column	Filter_Column
OPERAND_OUTPUT	m	10		0
OPERAND_OUTPUT	Km	0.1		0
OPERAND_OUTPUT	cm	1000		0
OPERAND_OUTPUT	dm	10		0
OPERAND_OUTPUT	dm	100	Value_Field > 9	1
OPERAND_OUTPUT	dm	1000	Value_Field > 10	2

Before running each statement, the system enriches the input data by filtering on the rows of the reference dataset by OPERAND_OUTPUT and multiplying the source values by the identified conversion factor.

Enriched source dataset¶

Operand_Field	Column_1	Column_2	Unit_Measurement	Value_Field
OPERAND_INPUT	North	Woman	m	500 = (50*10)
OPERAND_INPUT	North	Men	km	4 = (40*0.1)
OPERAND_INPUT	Center	Woman	m	700 = (70*10)
OPERAND_INPUT	Center	Men		30
OPERAND_INPUT	South	Woman	km	5.5 = (55*0.1)
OPERAND_INPUT	South	Men	m	350 = (35*10)
OPERAND_INPUT	East	Rather not say	dm	90 = (9*10)
OPERAND_INPUT	East	Rather not say	dm	1000 = (10*100)
OPERAND_INPUT	East	Rather not say	dm	11000 = (11*1000)

Calcolo Operand_output¶

The system copies the values to the output, keeping the splitting of logical dimensions.

Target Dataset¶

Operand_Field	Column_1	Column_2	Value_Field
OPERAND_OUTPUT	Woman	North	500
OPERAND_OUTPUT	Men	North	4
OPERAND_OUTPUT	Woman	Center	700
OPERAND_OUTPUT	Men	Center	30
OPERAND_OUTPUT	Woman	South	5.5
OPERAND_OUTPUT	Men	South	350
OPERAND_OUTPUT	Rather not say	East	90
OPERAND_OUTPUT	Rather not say	East	1000
OPERAND_OUTPUT	Rather not say	East	11000

Example 2

Source Dataset¶

Operand_Field	Column_1	Column_2	Unit_Measurement	Value Field
OPERAND_INPUT	North	Woman	m	50
OPERAND_INPUT	North	Men	Km	40
OPERAND_INPUT	Center	Woman	m	70
OPERAND_INPUT	Center	Men		30
OPERAND_INPUT	South	Woman	Km	55
OPERAND_INPUT	South	Men	m	35
OPERAND_INPUT	East	Rather not say	dm	9
OPERAND_INPUT	East	Rather not say	dm	10
OPERAND_INPUT	East	Rather not say	dm	11

Script¶

TGKML 1.0 [TARGET].[OPERAND_OUTPUT] := [SOURCE].[OPERAND_INPUT];

Reference dataset¶

Operand_Field	Unit_Measurement	Conversion_Factor	Filter_Column	Filter_Column
OPERAND_OUTPUT	m	10		0
OPERAND_OUTPUT	Km	0.1		0
OPERAND_OUTPUT	cm	1000		0
OPERAND_OUTPUT	dm	10		0
OPERAND_OUTPUT	dm	100	Value_Field > 9	1
OPERAND_OUTPUT	dm	1000	Value_Field > 10	2

Before running each statement, the system enriches the input data by filtering on the rows of the reference dataset by OPERAND_OUTPUT and multiplying the source values by the identified conversion factor.

Enriched source dataset¶

Operand_Field	Column_1	Column_2	Unit_Measurement	Value Field
OPERAND_INPUT	North	Woman	m	500 = (50*10)
OPERAND_INPUT	North	Men	km	4 = (40*0.1)
OPERAND_INPUT	Center	Woman	m	700 = (70*10)
OPERAND_INPUT	Center	Men		30
OPERAND_INPUT	South	Woman	km	5.5 = (55*0.1)
OPERAND_INPUT	South	Men	m	350 = (35*10)
OPERAND_INPUT	East	Rather not say	dm	90 = (9*10)
OPERAND_INPUT	East	Rather not say	dm	1000 = (10*100)
OPERAND_INPUT	East	Rather not say	dm	1000 = (10*100)

Calcolo Operand_output¶

The system copies the values on the output and then groups the rows with the same value for the "GENDER" logical dimension.

Target Dataset¶

Operand_Field	Column_1	Value Field
OPERAND_OUTPUT	Woman	125.5 = (500+700+5.5)
OPERAND_OUTPUT	Men	384 = (4+30+350)
OPERAND_OUTPUT	Rather not say	12090 = (90+1000+11000)