The Insert Column transformation in Nexadata Pipelines allows you to add new columns to your dataset using numeric calculations, default values, or simple string concatenation. This transformation is ideal for enhancing your dataset by performing calculations based on existing columns or adding default values such as dates or status labels. You can configure the transformation using Natural Language Mode or Advanced Mode, depending on your preferences.
Inputs for the Insert Column Transformation
Name of the Transformation: In Natural Language Mode, the transformation is automatically named, but it can be updated later using Advanced Mode. For instance, you might rename it to something like "Add Annual Expense Column" or "Insert Last Updated Column".
New Column Name: Define the name of the new column. For example, you could create a column called "Annual_Expense", "Net_Revenue", or "Last_Updated".
Column Formula or Default Value: Provide the logic for the new column. This could be a numeric calculation based on other columns (e.g., "Annual_Expense = Monthly_Rent + Utilities + Office_Supplies") or a default value (e.g., "LastUpdated = 2023-01-01").
Position of the New Column: Specify where the new column should be placed in your dataset:
First: Insert the column at the beginning.
Last: Add the column at the end.
Before/After: Place the new column before or after a specific existing column.
Reference Column: If you choose to position the column Before or After another column, you will need to select a reference column that will serve as the insertion point for the new column.
Using Natural Language Mode
In Natural Language Mode, you can describe the column you want to insert, and Nexadata will automatically create the transformation. The transformation name is also automatically generated, but you can update it in Advanced Mode if needed.
Example Instructions in Natural Language Mode
Add a column Annual_Expense with the formula Travel_Cost + Training_Fees.
Create a new column Annual_Sales by multiplying Monthly_Sales by 12.
Insert a column named LastUpdated with a default date value 01/01/2000.
Insert a column named Review_Status with a default text value Pending Review.
Add a column Total_Revenue with the formula Sales_Amount + Service_Fees.
Note: If Natural Language Mode doesn’t fully capture your needs, you can switch to Advanced Mode to refine the transformation.
Using Advanced Mode
In Advanced Mode, you have complete control over the Insert Column transformation setup. This mode allows you to manually define the column's name, formula, default value, and position within the dataset.
Steps in Advanced Mode
Name of the Transformation: Provide or update the transformation name, such as "Add Annual Expense Column" or "Insert Last Updated Column".
New Column Name: Define a unique name for the new column, such as "Annual_Expense", "Net_Revenue", or "Last_Updated".
Column Formula or Default Value: Specify the formula or logic for the new column. This can include numeric calculations like "Gross_Revenue - Expenses" or a default value such as a specific date or text (e.g., "Pending Review"). If you need to join text values, consider using the Merge transformation for string concatenation.
Position of the New Column: Select where the new column should be inserted:
First: Add the column at the start of the dataset.
Last: Add the column at the end of the dataset.
Before/After: If selecting Before or After, choose a reference column to specify the exact placement.
Reference Column: Provide a reference column if selecting Before or After.
Example in Advanced Mode
Name of the Transformation: Add Annual Expense
New Column Name: Annual_Expense
Column Formula: Travel_Cost + Training_Fees
Position: Last
Advanced Mode gives you more precise control over the configuration, allowing you to define exactly how the new column should behave.
Example Use Case
The Insert Column transformation is particularly useful when you need to create a new column based on numeric calculations from existing columns. For instance, if you want to calculate the total Annual_Expense by adding Travel_Cost and Training_Fees, you can use this transformation. For example:
Transformation Name: Add Annual Expense
New Column Name: Annual_Expense
Column Formula: Travel_Cost + Training_Fees
Position of New Column: Last
This transformation will create a new column called Annual_Expense at the end of your dataset, calculating the total expense for each record.
Summary
The Insert Column transformation in Nexadata Pipelines allows you to add new columns to your dataset by performing numeric calculations or setting default values. Use Natural Language Mode for quick setup or Advanced Mode for precise control. This transformation is ideal for adding calculated fields like revenue, expenses, or custom date values to enhance your dataset.