Skip to content
Infoveave Learning

Data Transformations with Calculated Columns

Mastering Data Cleansing with Calculated Columns in Infoveave

Learn on the dynamic capabilities of calculated columns in Infoveave, focusing on data cleansing techniques. By leveraging calculated columns, you can transform raw data into clean, standardized formats, enhancing the quality and reliability of your datasets. The lesson helps you understand essential data cleansing tasks, including a few from the below:

  • Changing Column Format: Modify the columns type to ensure consistency and compatibility using calculated columns.
  • Replace Nulls: Replace null values in datasets with specific default values or meaningful substitutes, preventing data inconsistencies and improving analysis accuracy.
  • Replace Column Values: Replace specific values in columns with alternative values, enabling standardization and normalization across datasets.
  • Change Column Names: Rename columns to provide clearer descriptions or align with organizational naming conventions, enhancing data understanding.
  • Remove Timestamps from Date Columns: Remove timestamps from date columns to extract only date information, for date-based analysis and visualization.
  • Add Conditional Filters: Apply conditional filters to selectively filter and cleanse data based on specified criteria.
  • Concatenate Columns: Combine multiple columns in a dataset to a single column, required for the analysis and visualization purposes.

Change Column Format

In data analysis, it is required to change the column format of the data to suit specific requirements or fit certain rules. With Infoveave, you can easily accomplish this using calculated columns, where we can use custom JavaScript to make transform data. In this tutorial, we will learn to add an alphabet as prefix to numeric values in a column.

Example: Let us consider the previously provided dataset containing sales information, including a “Customer ID” column with numeric values representing unique customer identifiers. In this example, we will add the alphabet “A” to each “Customer ID” value to create a new identifier format.

Dataset:

Dealer NameCustomer IDCustomer Name
Hirthe and Sons10048Christian Gidney
Hirthe and Sons10063Mortie O’Brogane
Sipes Group10069Regine Elce
Kuvalis Group10063Mortie O’Brogane

Here is how we can achieve this using Infoveave:

  1. Navigate to the Studio module in Infoveave and select Datasources from the drop-down options.
  2. Locate and choose the Datasource “Sales Data”.
  3. Click on the Edit Datasource button for the selected Datasource. This action will redirect you to the Datasource Designer.
  4. Within the Datasource Designer, find the option to add a calculated column. This is typically labeled as Add Calculated Column.
  5. Input the JavaScript code provided earlier to add the alphabet prefix “A” to each “Customer ID” value.

JavaScript Code

let customerID = @Customer ID; // Get the value of the Customer ID column
let newCustomerID = 'A' + customerID.toString(); // Concatenate 'A' with the Customer ID and convert to string
newCustomerID; // Return the new Customer ID
  1. Configure the settings for the calculated column, such as the column name, data type, and any other relevant options.
  2. After configuring the calculated column, validate the formula to ensure it is correct. Once validated, save the changes to add the calculated column to your dataset.
  3. Preview the dataset to see the newly added calculated column with the modified “Customer ID” values. Confirm that the changes are as expected and click on Add Column.
  4. If satisfied with the changes, reupload the data batch to apply the modifications to your dataset permanently.

(Image: https://help.infoveave.com/wp-content/uploads/2024/01/Changing-Column-Format.png)

Replace Nulls

In data preparation for analysis, it is common to encounter missing or null values that can affect the accuracy of insights. Addressing these gaps is crucial for meaningful analysis. Infoveave provides tools to handle such scenarios efficiently. In this tutorial, we will explore how to replace null values with a specified text, like “Not available,” using calculated columns in Infoveave.

Example: Consider a dataset containing customer information where some fields may have missing values. Let’s focus on the “Age” column and replace null values with “Not available.”

Customer NameEmail IDAgeMobile NumberLast Purchase DateTotal Purchases
Christian Gidney[email protected]987654321015-12-20235
Mortie O’Brogane[email protected]876543210920-11-20233
Regine Elce[email protected]35765432109810-12-20237
Mortie O’Brogane[email protected]30654321098705-11-20234

Here is how we can achieve this using Infoveave:

  1. Navigate to the Studio module in Infoveave and select Datasources from the drop-down options.
  2. Locate and choose the Datasource.
  3. Click on the Edit Datasource button for the selected Datasource. This action will redirect you to the Datasource Designer.
  4. Within the Datasource Designer, find the option to add a calculated column. This is typically labeled as Add Calculated Column.
  5. In the calculated column settings, enter the following JavaScript code:

JavaScript Code

let age = @Age; // Replace 'Age' with the actual column name
if (age === null || age === "") {
age = "Not available"; // Replace null or empty values with "Not available"
}
age; // Return the modified value
  1. Configure the settings for the calculated column, such as the column name, data type, and any other relevant options.
  2. After configuring the calculated column, validate the formula to ensure it is correct. Once validated, save the changes to add the calculated column to your dataset.
  3. Preview the dataset to see the newly added calculated column. Confirm that the changes are as expected and click on Add Column.
  4. If satisfied with the changes, reupload the data batch to apply the modifications to your dataset permanently.

Replace Column Values

In scenarios where you need to modify specific values in your dataset to align with updated information or new business requirements. With Infoveave’s calculated columns feature, you can easily achieve this by applying custom JavaScript code to manipulate your data. In this tutorial, we will learn how to replace specific values in a column using an example dataset where we want to update the dealer name “Sipes Group” to “New Spkies” due to a recent company merger.

Example: Consider a dataset containing dealer information where some fields may have wrong values. Let’s focus on the “Dealer Name” column and replace the dealer name “Sipes Group” to “New Spkies”.

Ship DateOrder PrioritySales ChannelDealer NameCustomer ID
2024-01-05LowOnlineHirthe and Sons10048
2024-01-10LowOnlineHirthe and Sons10063
2024-01-15LowOnlineSipes Group10069
2024-01-20HighOfflineKuvalis Group10063
2024-01-25HighOfflineMiller Inc10089

Here is how we can achieve this using Infoveave:

  1. Navigate to the Studio module in Infoveave and select Datasources from the drop-down options.
  2. Locate and choose the Datasource.
  3. Click on the Edit Datasource button for the selected Datasource. This action will redirect you to the Datasource Designer.
  4. Within the Datasource Designer, find the option to add a calculated column. This is typically labeled as Add Calculated Column.
  5. In the calculated column settings, enter the following JavaScript code.
let dealerName = @Dealer Name; // Access the value of the "Dealer Name" column
// Check if the dealerName is "Sipes Group", replace with "New Spkies", else keep the original value
if (dealerName === "Sipes Group") {
dealerName = "New Spkies";
}
dealerName; // Return the modified value
  1. Configure the settings for the calculated column, such as the column name, data type, and any other relevant options.
  2. After configuring the calculated column, validate the formula to ensure it is correct. Once validated, save the changes to add the calculated column to your dataset.
  3. Preview the dataset to see the newly added calculated column. Confirm that the changes are as expected and click on Add Column.
  4. If satisfied with the changes, reupload the data batch to apply the modifications to your dataset permanently.

Remove Timestamps from Date Columns

In data analysis, it is common that the date columns contain timestamps. In certain cases, you may need to remove the time stamp analysis or visualization. In this tutorial, we will demonstrate how to remove timestamps from date columns using Infoveave’s calculated columns feature. We will use an example dataset containing order information with timestamps and show how to extract and format the date portion only.

Example: Consider a dataset containing order information with timestamps in the “Order Date” column. We want to remove the timestamps and display only the date portion.

Order IDOrder DateShip DateOrder Priority
2000103-01-2024 00:0005-01-2024 00:00Low
2000107-01-2024 00:0010-01-2024 00:00Low
2000212-01-2024 00:0015-01-2024 00:00Low
2000318-01-2024 00:0020-01-2024 00:00High

Here is how we can achieve this using Infoveave:

  1. Navigate to the Studio module in Infoveave and select Datasources from the drop-down options.
  2. Locate and choose the Datasource.
  3. Click on the Edit Datasource button for the selected Datasource. This action will redirect you to the Datasource Designer.
  4. Within the Datasource Designer, find the option to add a calculated column. This is typically labeled as Add Calculated Column.
  5. In the calculated column settings, enter the following JavaScript code.
var time = @Order Date;
var newTime = time ? time.split(" ")[0] : new Date().toISOString().split("T")[0];
var parts = newTime.split("-");
var formattedDate = parts[2] + "-" + parts[1] + "-" + parts[0];
formattedDate;
  1. Configure the settings for the calculated column, such as the column name, data type, and any other relevant options.
  2. After configuring the calculated column, validate the formula to ensure it is correct. Once validated, save the changes to add the calculated column to your dataset.
  3. Preview the dataset to see the newly added calculated column. Confirm that the changes are as expected and click on Add Column.
  4. If satisfied with the changes, reupload the data batch to apply the modifications to your dataset permanently.

Add Conditional Filters

In data analysis, filtering data based on specific conditions is a common task to focus on relevant subsets of data. With Infoveave, you can easily implement conditional filters using JavaScript code within calculated columns. This tutorial will demonstrate how to create a conditional filter to select rows based on a specific condition using the example of filtering customers older than 30 years old from the dataset.

Example: Consider a dataset containing employee information with columns for “Employee Name” and “Age”. We want to filter out employees who are older than 30 years old.

Customer IDCustomer NameEmail IDAgeMobile Number
10048Christian Gidney[email protected]329876543210
10063Mortie O’Brogane[email protected]288765432109
10069Regine Elce[email protected]357654321098
10063Mortie O’Brogane[email protected]306543210987

Here is how we can achieve this using Infoveave:

  1. Navigate to the Studio module in Infoveave and select Datasources from the drop-down options.
  2. Locate and choose the Datasource.
  3. Click on the Edit Datasource button for the selected Datasource. This action will redirect you to the Datasource Designer.
  4. Within the Datasource Designer, find the option to add a calculated column. This is typically labeled as Add Calculated Column.
  5. In the calculated column settings, enter the following JavaScript code.
var age = @Age;
if (age && age <= 30) {
true;
} else {
false;
}
  1. Configure the settings for the calculated column, such as the column name, data type, and any other relevant options.
  2. After configuring the calculated column, validate the formula to ensure it is correct. Once validated, save the changes to add the calculated column to your dataset.
  3. Preview the dataset to see the newly added calculated column. Confirm that the changes are as expected and click on Add Column.
  4. If satisfied with the changes, reupload the data batch to apply the modifications to your dataset permanently.

Conclusion

The lesson covers on data cleansing with calculated columns in Infoveave. Change column formats, replace nulls or specific values, and add conditional filters with custom JavaScript in Infoveave. Transform raw data into clean, standardized formats, enhancing dataset quality and reliability for improved analysis all with calculated columns.