Description:

The Transform Number Operations step performs configurable mathematical operations on one or more numeric columns within an ETL dataset. The transformation is configured through the Configure Transform List property, where ETL designers define one or more numeric operation rules.

Many enterprise ETL processes require numeric values to be adjusted before they are consumed by downstream applications. Examples include increasing prices by a percentage, calculating discounts, converting measurement units, applying tax rates, scaling financial values, or deriving calculated metrics. This transformation provides a configurable mechanism for applying these calculations without requiring custom scripting.

The configured operation is applied to each selected numeric column, producing updated or calculated values that become immediately available to downstream ETL components.

Unlike Transform Number Range, which classifies numeric values into predefined categories, Transform Number Operations performs arithmetic calculations that modify the numeric values themselves.

The Transform Number Operations step is typically placed after importing and validating numeric data but before reporting, aggregation, exporting, or database loading.

The step supports:

• Arithmetic calculations
• Multiple numeric transformation rules
• Numeric scaling
• Value normalization
• Business calculations
• Enterprise ETL workflows
• Integration with FlowWright ETL pipelines

Typical business uses include:

• Price calculations
• Tax computation
• Currency conversion
• Unit conversion
• Financial adjustments
• KPI calculations
• Business metric generation

Usage:

The Transform Number Operations step is typically inserted after importing and validating numeric data but before aggregation, reporting, exporting, or loading the data into downstream business systems.

During execution, each configured numeric column is processed according to its associated operation rule. The resulting calculated values immediately become available for subsequent ETL transformations.

A typical ETL pipeline might look like this:

Typical usage scenarios include:

• Applying a 10% price increase
• Calculating tax-inclusive amounts
• Converting kilograms to pounds
• Calculating employee bonuses
• Scaling imported financial values
• Generating sales commissions
• Preparing metrics for executive dashboards

The calculated values can then be validated, aggregated, filtered, grouped, exported, or loaded into downstream systems.

Prerequisite:

The ETL process engine service should be running to execute the ETL definitions.

Example:

Let’s build and execute the “clsTransformNumberOperationsDef” example.          

• Create a new ETL definition named “clsTransformNumberOperationsDef” and open it in designer mode.
• Drag “clsInputCSV, clsTransformNumberOperations, and clsOutputCSV” steps to the canvas. 
• Connect the dots between the “clsInputCSV” and other steps, as shown above.
• Define a variable or a global variable to hold the virtual path.
• Click the "clsInputCSV" step to configure its "Required" properties. Provide a name for the step. Specify the path to the input CSV file on the application server. Select the ETL data schema from the drop-down list. Note: The schema's column names should match those in the input file. Click the Save button. Click here to learn about the ETL Data Schema Designer.

• Click the "clsInputCSV" step to view its "Optional" properties. Select the Show Schema button. The pop-up window displays the schema columns as shown below. This function helps to understand the schema at a glance.  

• Click the "clsTransformNumberOperations" step to configure its "Required" properties. Provide a name for the step. Click the button to configure the columns and operation type, then click the Save button. 

• Click the "clsTransformNumberOperations" step to configure its "Required" properties. Click the button to configure the transform list. A pop-up window will appear for configuration. Click the Add Row (+) button to insert an empty row. Select the first column from the drop-down list. Select the number operation (add, subtract, multiply, divide). Select the second column from the drop-down list. Select the Out column from the drop-down list. You may also type a new Out column name and press Enter to add it. Click the Save button. You may add multiple columns for transformation by using the Add Row button. 

• Click the "clsOutputCSV" step to configure its "Required" properties. Enter a name for the step. Enter the path to the output CSV file on the application server. Click the Save button. 

• Click the "clsOutputCSV" step to configure its "Optional" properties. Provide a name for the step and specify the variable or global reference to hold the virtual output path. Click the Save button. 

• The “Logging” configuration is necessary for documentation and also measures workflow progress and percent complete. This is achieved by configuring the step state and percent fields individually, as shown below. Configure the “Logging” using the following properties.

• Save the ETL definition, create a new ETL instance, and execute. Render the ETL instance. Click the ETL step to view its properties. The “clsTransformNumberOperations” step should format and transform the numbers as configured and write them to the output file. The variable/global holds the output file’s virtual path, as shown below. 

Tips:

• Validate numeric values before applying mathematical operations.
• Clearly document business formulas so they remain consistent across ETL definitions.
• Preserve the original numeric values by duplicating the source column if audit or comparison requirements exist.
• Verify calculation precision and rounding requirements before deployment.
• Test numeric operations using representative production datasets to confirm expected results.
• Review downstream reports whenever calculation logic changes.
• Combine this transform with Validate Data, Transform Number Range, Group By Transform, Sorting Transform, Output Column Transform, JSON Output File, or SQL Output components to create comprehensive analytics and reporting workflows.

Notes:

• Multiple numeric operation rules can be configured within a single transformation.
• Source columns must contain valid numeric values.
• Mathematical operations are applied according to the configured transformation rules.
• Calculated values immediately become available to downstream ETL components.
• Invalid numeric values or transformation configurations may result in ETL execution errors.
• Division operations should be designed to avoid divide-by-zero scenarios where applicable.
• This transform is commonly used for financial processing, pricing calculations, taxation, inventory valuation, ERP migration, CRM reporting, KPI generation, business intelligence, and enterprise analytics.
• Review downstream calculations whenever business formulas or operational rules are modified.

Transform Number Operations vs. Transform Number Range:

Although both transformations operate on numeric data, they are designed for different purposes:

As a general guideline:

• Use Transform Number Operations when numeric values must be mathematically calculated or adjusted.
• Use Transform Number Range when numeric values should be grouped into predefined business classifications.

Definition Sample:

You may download the sample ETL definition(s) from the link provided and later import them into your FlowWright ETL Designer.

Note: Verify and complete any missing configuration after importing the sample, including:

• Numeric operation rules
• Source column mappings
• Mathematical operation parameters
• ETL schema validation
• Precision and rounding requirements
• Downstream transformation mappings
• Destination component configuration
• Environment-specific settings

After verifying the configuration, save the ETL Definition before execution.

Click here to download the sample file.