Skip to main content

Add Calculated Column

1. Function Description

Adding calculated columns refers to introducing new columns into the original data and generating column values through specific calculation or transformation rules. It covers various operations such as mathematical operations based on existing columns, conditional judgments, string concatenation, and more.

By extending or merging calculations on existing columns in the dataset, new analysis indicators can be created based on existing data, thereby providing a more comprehensive data view.

For example, in a product sales table, you can add a total sales amount by creating a new total sales field whose value equals sales amount multiplied by sales quantity. Or use time functions to extract order time, such as DATE_TRUNC(week,[order_time]), which returns the first day of the week corresponding to the "order_time" field of that row.

|550

2. Operation Steps

  1. Drag the "Add Calculated Column" operator from the data flow operator area into the right canvas editing area.

  2. Click the "Add Calculated Column" operator and click "Add Calculated Field" (supports adding multiple fields).

    • Enter field name, select field type, then write the calculation formula;
    • Field types support numeric, text, date, date and time, and boolean.
    • Calculation formulas support four arithmetic operations, combined with functions as needed.

  3. Supports using time macro parameters, global parameters, and workflow parameters.

  4. Click "Confirm", and at the current node, click preview to confirm the data results.

3. Specific Case

The following introduces configuring a Year as an example (function usage).

  1. Drag the "Add Calculated Column" operator from the data flow operator area into the right canvas editing area and connect it to the upstream node;

  2. Click the "Add Calculated Column" operator. The left area becomes the current operator configuration area. Rename it according to business needs, for example "Year Month";

  3. Click "Add Calculated Field" to edit the calculated field:

    • Function: COUNT(), YEAR()
    • Field: Date
    • Field Name: Year
    • Select Field Type: Text

    Note: We support 8 function types, including but not limited to aggregate statistics, non-aggregate statistics, date and time, etc.

    In ETL, why does using sum to sum up new fields report errors?

  4. Click "OK" and preview the data results to ensure that the calculated column values meet expectations and do not contain errors or abnormal values.