Function Description of New Calculation Column

Version

Function Description

You can add a New Calculation Column operator to create a field by referencing or calculating existing fields without affecting them, as shown in the following figure.

Notes

You may encounter the following problems when using a New Calculation Column operator with MySQL as the data source.

• After you round 600.185 and 700.185 to two decimal places respectively, the results after rounding are different, which are 600.18 and 700.19.

• After you multiply 4131.9 by 0.45, the normal calculation result is 1859.355, while the result of the New Calculation Column operator is 1859.3549999999998.

In the New Calculation Column operator, numerical calculations are performed using the double type, which may cause some precision issues.

You are advised to adjust the field type to decimal and use a Spark SQL operator for calculations.

Scenario Description

During the table data processing, you may want to add a column to record the time difference between the contract signing time and the registration time.

You can download the example data: Contract.xlsx.

You have a table named Contract Information Table. The table contains the Registration Time and Contract Signing Time fields, as shown in the following figure.

Scheduled Task Creation

1. Create a scheduled task and drag a Data Transformation node onto the page, as shown in the following figure.

Data Input

Enter the Data Transformation node, add a DB Table Input operator, and configure the operator to retrieve the data of Contract Information Table stored in the database, as shown in the following figure.

Column Adding

1. Drag a New Calculation Column operator onto the Data Transformation editing page, and configure the operator to add a column with Time_Difference as the field name. The field value is the time difference between the contract signing time and the registration time, as shown in the following figure.

After you click OK, the final result is shown in the following figure.

Note:

1. You can add and delete fields, but cannot sort fields on this page.

2. Added fields will be appended to the end of the original fields in the order of the list. If you want to adjust the field order, you can enter the DB Table Output editing page and adjust the order in Field Mapping.

The following table describes the setting items.

Setting Item	Description
Field Name	The name of a new field, which is empty by default and required. The field name cannot be empty. The length must be within 30 characters. The field name can only consist of letters (from a to z, which is case-sensitive), digits (from 0 to 9), underscores (_), and Chinese characters. Spaces are not allowed. The name of a new field cannot duplicate the names of existing fields or other new fields.
Field Value	Displays the field value, which is empty by default and required. The formula editing box will directly pop up after you click Add Field Value.
Formula Editing Box	The supported functions are displayed on the left side of the formula editing box. When you hover the cursor over a function, the description of the formula and parameter, and examples are displayed in the formula explanation area. When you click a formula on the left, the system will automatically insert the corresponding formula into the formula editing box. Note: 1. Fields and parameters in the formula editing box need to be selected from the left or entered using auto-complete. If you want to input them manually, you need to wrap parameters with ${} and fields with #{} to make them take effect. The system will highlight parameters and fields in red if they are invalid. (Note that case sensitivity applies.) 2. If the calculation formula for new column values contains a parameter and the parameter value is a decimal, the system will truncate the parameter value and use only the integer part for calculation.
Field Type	1. The field type is displayed based on the result type of the field calculation and cannot be manually specified. If you want to perform type conversion, you can use relevant conversion formulas or add a Field Setting operator to achieve the conversion. 2. When the system cannot automatically recognize the output result type (for example, when NULL is manually entered), you can specify the type as needed. Note: For FineDataLink versions earlier than 4.1.11.3, the numeric type defaults to the double type. FineDataLink of V4.1.11.3 and later versions supports field types including bool, long, double, string, and timestamp.

2. Click Data Preview, as shown in the following figure.

Subsequent Data Processing

You can continue to process the data in Contract Information Table or add a DB Table Output operator to output the data to the database.

Usage Example 

You can learn about the use of the New Calculation Column operator through the following content.

Others

The New Calculation Column operator does not support the SUM() and WINDOW() functions. You are advised to use a Spark SQL operator to realize these functions. For details, see Spark SQL Window Function.