In DataPrep

Enrich your data by inserting new calculated fields using our advanced scripting editor with autocomplete.

In DataPrep, you can create custom calculated columns based on existing data.

For example, you might have a dataset with "Sales" and "Expenses" columns and want to calculate the "Profit" as a new column:

You can add a new column 'profit' and then write the following script:

return SalesColumn - ExpensesColumn;

Here, the calculated column "Profit" is derived by subtracting the "Expenses" column from the "Sales" column.

Examples

Example 1: Concatenate First and Last Name in New Column

Description: This script concatenates the "First Name" and "Last Name" columns and returns a single string containing both names in a new column 'Full Name'.

Code:

// Concatenate First and Last Name
Col_firstName + " " + Col_lastName

---

Example 2: Create a New LocalDateTime Column

Description: This script parses the string values in the "Order Date" column, assuming they are in the format "yyyy-MM-dd HH:mm:ss," and converts them into LocalDateTime objects in a new column.

Code:

// Create a New LocalDateTime Column by Converting the String Values
// in "Order Date" Column to LocalDateTime Type
// Sample input: "2009-09-13 14:40:33"
LocalDateTimeUtil.parse(Col_orderDate, "yyyy-MM-dd HH:mm:ss")

---

Example 3: Create a New Column with the Quarter for the "Order Date"

Description: This script determines the quarter for the "Order Date" based on the month and creates a new column with the quarter information.

Code:

// Create a new column with the quarter for the "Order Date"
// Assuming you have "Order Date" column of type LocalDateTime
var month = LocalDateTimeUtil.getMonth(Col_orderDate);
if (month == 1 || month == 2 || month == 3) {"Q1";}
else if (month == 4 || month == 5 || month == 6) {"Q2";}
else if (month == 7 || month == 8 || month == 9) {"Q3";}
else if (month == 10 || month == 11 || month == 12) {"Q4";}
else {"";}

---

Example 4: Reformat a Date String

Description: In this example, you have retrieved data with a date in a less user-friendly format, such as "2023-10-25T12:22:05.2247439Z." To improve data management and enhance readability, you will reformat the date information and introduce a new 'date' column.

Code:

// Parse the date string into an Instant
var instant = Instant.parse(datetime);

// Extract the year, month, and day components from the Instant
var year = InstantUtil.getYear(instant);
var month = InstantUtil.getMonth(instant);
var day = InstantUtil.getDayOfMonth(instant);

// Create a LocalDate object from the extracted components
return LocalDate.of(year, month, day);