computedColumn

Overview

The computedColumn logic in Vantage is designed to enhance data manipulation capabilities by allowing users to create new columns in their datasets derived from expressions that operate on existing columns. This functionality supports a range of arithmetic operations, string manipulations, date functions, and conditional logic, enabling users to perform complex calculations directly within the data processing flow. A sandboxed expression evaluator is utilized to ensure safety and security during the evaluation of expressions.

Purpose

The primary purpose of the computedColumn is to enable users to derive new insights and calculated metrics from existing data columns. By specifying expressions, users can create columns that automatically reflect changes to the underlying data, facilitating dynamic analysis and reporting.

How It Works

1. Input Handling:

   • The computedColumn function accepts inputs and extracts the required data from the input structure.
   • It validates the input data, ensuring it is in the correct format (i.e., an array of objects).

2. Configuration Parsing:

   • The configuration determines which new columns will be created based on specified expressions.
   • The expressions are compiled into evaluators that can be applied to each row of data.

3. Data Transformation:

   • For each row in the input data, the compiled expressions are applied, and new columns are generated accordingly.
   • If an expression fails during evaluation, the corresponding new column value is set to null.

4. Output Generation:

   • The result is an array of data objects that includes the original columns along with any newly computed columns.

Settings

The configuration for the computedColumn contains the following settings:

Columns

1. Setting Name: columns
   • Input Type: Array of Objects
   • Description: A list of objects where each object defines a new column to be created. Each column has a name and an expression.
     • name: The name of the new column (string).
     • expression: The expression is evaluated for each row to compute the new column value.
   • Impact of Changes: Adding or modifying items in the columns array determines which new columns will be created and how their values will be derived. If the expression defines calculations or transformations accurately, the column will reflect correct data. Otherwise, it may result in null values.
   • Default Value: An empty array [] (indicating no new columns are created).

Example Configuration

An example configuration for the computedColumn node could look like this:

{
  "columns": [
    { "name": "profit", "expression": "revenue - cost" },
    { "name": "full_name", "expression": "CONCAT(first_name, \" \", last_name)" },
    { "name": "year", "expression": "YEAR(order_date)" }
  ]
}

Data Expectations

The computedColumn expects the following data structure:

• Input Data: An object containing at least one input array, referred to as input1.
• Data Structure: Each entry in this array should be an object representing a row of data with properties (columns) available for evaluation against the defined expressions.

Example row data to be processed:

[
  {
    "revenue": 150,
    "cost": 100,
    "first_name": "John",
    "last_name": "Doe",
    "order_date": "2023-10-01"
  },
  {
    "revenue": 200,
    "cost": 80,
    "first_name": "Jane",
    "last_name": "Doe",
    "order_date": "2023-09-15"
  }
]

Use Cases & Examples

Use Cases

1. Financial Reporting:

   • Businesses can use computedColumn to derive financial metrics such as profit margins or total expenditure without adjusting the base data, enhancing the analytics process during periodic financial reviews.

2. Customer Data Enrichment:

   • Organizations could concatenate first and last names for customer lists, ensuring a more professional appearance in reports and communications.

3. Time-Series Analysis:

   • Analysts can extract year, month, or other date components from timestamps to enable trend analysis over time.

Detailed Example Configuration

Use Case: Financial Reporting

Objective: Calculate profit based on revenue and cost.

Configuration:

{
  "columns": [
    { "name": "profit", "expression": "revenue - cost" }
  ]
}

Input Data:

[
  {
    "revenue": 150,
    "cost": 100,
    "first_name": "John",
    "last_name": "Doe",
    "order_date": "2023-10-01"
  },
  {
    "revenue": 200,
    "cost": 80,
    "first_name": "Jane",
    "last_name": "Doe",
    "order_date": "2023-09-15"
  }
]

Expected Output:

[
  {
    "revenue": 150,
    "cost": 100,
    "profit": 50
  },
  {
    "revenue": 200,
    "cost": 80,
    "profit": 120
  }
]

In this example, the profit is calculated for every row based on the revenue and cost provided, allowing for immediate insight into financial performance.

Additional Details

AI Integrations

The computedColumn relies on a lightweight expression evaluator that is constructed to be safe and secure, ensuring that no direct execution of potentially harmful code (such as using eval) occurs. It can be used in conjunction with AI models for predictive analytics or decision support but does not directly integrate any AI functionalities itself.

Billing Impacts

While the computedColumn functionality does not inherently incur additional costs, the volume of data processed and the complexity of the expressions could influence operational costs, particularly in environments where processing power or data storage is billed on a usage basis. Users should monitor their configurations for efficiency to mitigate unnecessary expenses.