You can add a New Calculation Column operator to create a field by referencing or calculating existing fields without affecting them.
This document introduces the composition and correct writing format of the formulas in a New Calculation Column operator.
The formula calculation consists of four components:
Content
Description
Function
A statement used to convert the value or member of a field
Field
Fields from your data tables or parameters already set in FineDataLink
Operator
A symbol that indicates the operation to be performed
Constant
A constant value represented exactly as written in the formula
For example, consider the following calculation:
IF(Contract Amount > 2000, "High Value_Order", "Low Value_Order")
The following table shows the corresponding calculation details.
IF
Contract Amount
For details, see the "Operator Syntax" section.
String: "High-Value_Order" and "Low-Value_Order"
Numeric value: 2000
For details about how to use each component in a calculation and set the corresponding format, see the following sections.
In FineDataLink, functions are the main components of calculations.
1. Functions are displayed in blue, each with a specific syntax.
For example, add a New Calculation Column operator in the Data Transformation node of a scheduled task, click Add Field Value, and a function list will appear. Next to the function list is the field selection area. When you select a function from the function list, the rightmost section will update to display the required syntax and description of the function, along with one or more examples, as shown in the following figure.
2. Multiple functions can be used in the calculation. For example: IF(SIGN(Profit)=1,Profit,0).
Two functions, IF() and SIGN(), are involved in the calculation.
The above example is a case where one function is included (nested) in another. In this case, the SIGN() function for Profit will be calculated before the IF() function because the SIGN() function is inside the parentheses.
You can insert fields into the calculation. The function syntax usually indicates where to insert the fields. For example: CONCATENATE(Text 1, Text 2, ...)
For example, if you want to join the Owner_Country and Owner_City fields, enter the formula CONCATENATE(Owner_Country,Owner_City) by selecting the required fields from the left field selection area, as shown in the following figure.
To perform a calculation, you need to know the supported operators. Operators are displayed in black in calculations.
+
Value addition
Date calculation
Text concatenation
Text + Text / Value / Date / Boolean
If any parameter is of text-type, text concatenation is performed.
Date-type parameters will be converted into strings in the YYYY-MM-dd HH:mm:ss format.
Boolean. FALSE is treated as 0, and TRUE is treated as 1 in calculations.
During text concatenation,, if any operand is NULL, it will be considered as an empty string for concatenation.
If the values of all operands are NULL, the result will be NULL.
Date + value X (value X + date) (with the same logic as ADDTODATE())
Calculates the date that is X days after the specified date.
If the result is a decimal, the fractional part will be converted into hours, minutes, and seconds accordingly.
If the value of any operand is NULL, the result is NULL.
Value X + value Y
3 + 3
1. Text + text / value / date / Boolean: text type
2. Date + value X (value X + date): date type
3. Value X + value Y: value type
/
Value division
Value / value: value division
15/3
Value type
If the divisor is 0, the result is NULL.
*
Value multiplication
Value * value: value multiplication
3 * 3
-
Value subtraction
Value X - value Y: value subtraction
Date - value X (with the same logic as ADDTODATE()): calculates the date that is X days before the specified date
Date - date: calculates the number of milliseconds between the specified dates
3 – 1
1. Value X - value Y: value type
2. Date - value X: date type
3. Date - date: value type
4. If the value of any operand is NULL, the result is NULL.
>
<
>=
<=
=
==
!=
Comparison operation
Text, Value, Date, and Boolean
1. >, <, >=, and <=: Both sides of the operator must be of the same type. Automatic conversion is not supported.
2. =, ==, and !=: No type requirement for both sides of the operator. Values of different types are considered unequal. Only when the types are consistent will the value comparison be carried out.
= and == are considered as equivalent inputs.
A1 > B1
A1 < B1
A1 >= B1
A1 <= B1
A1 = B1
A1 == B1
A1 != B1
Boolean
The system first judges the type and then checks whether NULL values exist.
!
Logical NOT
FALSE is treated as 0, and TRUE is treated as 1 in calculations.
For value-type data:
Non-zero values are considered true.
Zero values are considered false.
!A: If A is true, !A is false.
()
Increases operation precedence
Text expressions can be used to represent constant values as written. When using functions, you may need to use text expressions to represent values such as numbers and strings.
Text
Content enclosed in double quotes marks
Value
Numbers written without any symbols
NULL
Simply written as NULL
Case-insensitive
Simply written as TRUE or FALSE
滑鼠選中內容，快速回饋問題
滑鼠選中存在疑惑的內容，即可快速回饋問題，我們將會跟進處理。
不再提示
10s後關閉
Submitted successfully
Network busy