Description:

The Transform Number Range step converts numeric values into configurable ranges or classifications based on predefined thresholds. The transformation is configured through the Configure Transform List property, where ETL designers define one or more number range mappings.

Many enterprise datasets contain raw numeric values that are more meaningful when grouped into business categories. For example, employee ages can be grouped into age bands, sales amounts into revenue tiers, customer balances into risk categories, or inventory quantities into stock level classifications. This transformation simplifies those classifications without requiring custom scripting.

The Transform Number Range step evaluates each configured numeric value against the defined range rules and assigns the corresponding output classification or formatted value.

Unlike Transform Padding, which modifies the presentation of numeric text, this transformation evaluates numeric values against configurable thresholds to produce business-friendly classifications.

The Transform Number Range step is commonly placed after data validation and before aggregation, reporting, business intelligence processing, or exporting.

The step supports:

• Configurable numeric ranges
• Multiple range definitions
• Numeric classification
• Business rule mapping
• Enterprise ETL workflows
• Integration with FlowWright ETL pipelines

Typical business uses include:

• Customer segmentation
• Sales performance classification
• Inventory level categorization
• Financial reporting
• Credit and risk scoring
• KPI dashboards
• Business intelligence reporting

Usage:

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

During execution, each configured numeric value is compared against the configured range definitions. When a value falls within a defined range, the corresponding output value or classification is assigned and becomes available for subsequent ETL components.

A typical ETL pipeline might look like this:

Typical usage scenarios include:

• Categorizing customers by annual revenue
• Grouping employee ages into reporting bands
• Assigning inventory stock levels
• Creating sales performance categories
• Classifying customer balances
• Preparing executive dashboards
• Supporting business intelligence reporting

The classified values can then be grouped, filtered, aggregated, 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 “clsTransformNumberRangeDef” example.          

• Create a new ETL definition named “clsTransformNumberRangeDef” and open it in designer mode.
• Drag the “clsInputCSV, clsTransformNumberRange, and clsOutputCSV” steps to the canvas. 
• Connect the dots between the “clsInputCSV” and other steps, as shown above.
• Define a variable or a global 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 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 "clsTransformNumberRange" step to configure its "Required" properties. Provide a name for the step. Click the button to configure the columns and operation type, then click Save. 

• Click the "clsTransformNumberRange" 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 column from the drop-down list for the minimum and maximum values. Select the Out column from the drop-down list. You may also type a new Out column name and press the Enter key 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 “clsTransformNumberRange” step should transfer the configured number range to the output file. The variable/global holds the output file’s virtual path, as shown below. 

Tips:

• Validate numeric values before applying range transformations to ensure accurate classifications.
• Define non-overlapping ranges to prevent ambiguous results.
• Document business range definitions so they remain consistent across ETL solutions.
• Review range thresholds whenever business policies change.
• Test range configurations using representative production datasets before deployment.
• Consider creating a separate output column if both the original numeric value and its classification are required.
• Combine this transform with Validate Data, Group By Transform, Filter with Conditions, Sorting Transform, Output Column Transform, JSON Output File, or SQL Output components to create comprehensive reporting and analytics workflows.

Notes:

• Multiple range definitions can be configured within a single transformation.
• Source columns must contain valid numeric values.
• Range evaluation follows the configured transformation rules.
• Successfully classified values immediately become available to downstream ETL components.
• Invalid range definitions or column mappings may result in ETL execution errors.
• This transform is commonly used for financial reporting, KPI dashboards, customer segmentation, inventory management, ERP migration, CRM reporting, business intelligence, and enterprise analytics.
• Review downstream reporting whenever business thresholds or classification rules are modified.

Transform Number Range vs. Transform Padding:

Although both transformations can operate on numeric data, they serve different purposes:

As a general guideline:

• Use Transform Number Range when numeric values must be categorized into business-defined ranges.
• Use Transform Padding when values must conform to fixed-length formatting requirements.

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:

• Number range definitions
• Source column mappings
• Classification values
• ETL schema validation
• 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.