In DataPrep

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);

Examples​

Examples