Step 2: Reformat the Join_Date Values and define added fields
PreviousStep 1: Create the ETL pipelineNextMultiple Executions, Time Duration, Linked App and Execution Status
Last updated
Last updated
The joining date for the customer in this file is in the format (M/DD/YYYY), whereas the format of the Join_Date in the entity is (MM/DD/YYYY). The date format must be reformatted before storing it in the entity. The second step of this exercise is
Reformat the Join_Date values.
Assign values to the added fields (DateMonthJoinDate, Customer_Value, and Rating).
To define the actions, go to the Code tab>On Reading record function and expand it. To add an “Action box”, click the [+ Action box] button.
In this Action box, the format of the join date will be aligned to the format of the destination entity. To give it a more meaningful label, click on the text and enter the text as “fix join date field”.
To define the variables to store values in this Action box, go to the “Variables” tab and add a new variable by clicking the [+ Variable] button.
To store the “Join_Date” value in the Reader as string before reformatting it, define the first variable as follows: Variable name: “strList”, Data Type: “string”, Select the “[List]” checkbox. It displays as “List”. This defines the variable to store a list of strings.
To store the value of the Day from the “Join_Date”, add and define the second variable as follows: Variable name: “strDay”, Data Type: “string”
To store the value of the Month from the “Join_Date”, add and define the third variable as follows: Variable name: “strMonth”, Data Type: “string”
To store the value of the Year from the “Join_Date”, add and define the fourth variable as follows: Variable name: “strYear”, Data Type: “string”
To store the value of the adjusted DateTime of the “Join_Date”, add and define the last variable of the “Action box” as follows: Variable name: “adjustedDateTime”, Data Type: “string”
To define the actions in the “Action box”, go to the “Code” tab and add an Action.
To define the action to store the Join_Date as a list of strings:
Select Action as “Split string”.
Select Target Value as “strList”.
Select Source as Variables>reader>Join_Date. It displays as “reader.Join_Date”.
Enter Delimiter as “/” (without quotes). (the date format in the source file is “d/mm/yyyy hh:mm:ss”, hence, the list will be created by separating the digits based on the “/”)
To update the month's value (first part of the list of strings) into a variable, add and define the second Action as follows:
Select Action as “Update Variable”.
Select Target Value as “strMonth”.
Select Value as Variables>strList>getFirst(). It displays as “strList.getFirst()”.
To update the format of the “strMonth” value to align it with that of the “Join_Date” in the entity, i.e., convert the format from “m/” to “mm/”, add and define the third Action:
Select Action as “Condition action”.
Enter the description as “if month is one digit”.
To define the criteria, go to the “Criteria” tab in the “Condition action” and add a new Criteria bar.
Define the Criteria bar to check the value of “strMonth” is less than 2 digits:
Select Source as Variables>strMonth>length(). It displays as “strMonth.length()”.
Select Operator as “Is Equal”.
Enter Integer value as “1” and press Enter.
To define the action if the Criteria Bar returns true, go to the Code tab>On True section. Add and define the action to update the “strMonth” variable with a “0” placed before the value:
Select Action as “Update Variable”.
Select Target Value as “strMonth”.
Select Source as follows to concatenate the “0” with the value of “strMonth”:
Enter the digit “0” (without quotes) and press Enter.
Select Variables>strMonth. The source field displays as “0”“strMonth”.
To store the second item of list of strings (day's value) in the variable “strDay”, add and define an action after the “Condition action” as follows:
Select Action as “Get List item”.
Select Target Value as “strDay”.
Select Source as Variables>strList. It displays as “strList”.
Enter the index position as “1” (without quotes) and press Enter.
To update the format of the “strDay”value to align it with that of the “Join_Date” in the entity i.e convert the format from “d/” to “dd/”, add and define the third action:
Select Action as “Condition action”.
Enter the description as “if day is one digit”.
To define the criteria for the “Condition action”, go to the “Criteria” tab in the “Condition action” and add a new Criteria bar.
Define the Criteria bar to check the value of “strDay” is less than 2 digits:
Select Source as Variables>strDay>length(). It displays as “strDay.length()”.
Select Operator as “Is Equal”.
Enter Integer value as “1” and press Enter.
To define the action if the Criteria bar returns true, go to the Code tab>On True section, add and define the action to update the “strDay” variable with a “0” placed before the value:
Select Action as “Update Variable”.
Select Target Value as “strDay”.
Select Value as follows to concatenate the “0” with the value of “strDay”:
Enter the digit “0” (without quotes) and press Enter.
Select Variables>strDay. It displays as “strDay”. The source field displays as “0”“strDay”.
To separate values of the year and time, the last part of the first list of strings needs to be split further. After the Condition action, add and define the Action as follows:
Select Action as “Split string”.
Select Target Value as “strList”.
Select the Source as Variables>strList>getLast(). It displays as “strList.getLast()”.
Enter the Delimiter as “ ” (space without quotes). (The list of strings to split is the last part of the first string i.e. “yyyy hh:mm:ss”, hence, the list of strings will be created by separating the values based on the “(space)”.)
To update the year's value (first part of the list of strings created in the previous step) into a variable, add and define the second action as follows:
Select Action as “Update Variable”.
Select Target Value as “strYear”.
Select the Value as Variables>strList>getFirst(). It displays as “strList.getFirst()”.
To update the values of day, month and year in the Join_Date read by the Reader into the format of the Join_Date field in the entity (yyyy-mm-dd hh:mm:ss), add and define an Action as follows:
Select Action as “Update Variable”.
Select Target Value as reader>Join_Date(). It displays as “reader.Join_Date()”.
Select Value as follows:
Select Variables>strYear. It displays as “strYear”.
Type “-” (without quotes) and press Enter.
Select Variables>strMonth. It displays as “strMonth”.
Type “-” (without quotes) and press Enter.
Select Variables>strDay. It displays as “strDay”.
Type “(space)” (without quotes) and press Enter.
Type “00-00-00” (without quotes) and press Enter. The Source value displays as “strYear”“-”“strMonth”“-”“strDay”“(space)”“00-00-00”
To update the values of the Day and Month in the reader field of “DayMonthJoinDate”, add and define an Action as follows:
Select Action as “Update Variable”.
Select Target Value as reader>DayMonthJoinDate().
Select Value as follows:
Select Variables>strDay. It displays as “strDay”.
Select Variables>strMonth. It displays as “strMonth”.
The Source value displays as “strDay”“strMonth”.
To assign a value to “Customer_Value” field added to the Reader, add and define an Action as follows:
Select Action as “Update Variable”.
Select Target Value as reader>Customer_Value. It displays as “reader.Customer_Value”.
Type the Value as “0” and press Enter.
To assign a value to “Rating” field added to the Reader, add and define an Action as follows:
Select Action as “Update Variable”.
Select Target Value as reader>Rating. It displays as “reader.Rating”.
Type the Value as “1” and press Enter.
To save the ETL pipeline, click [Save].
To publish the ETL pipeline, click [Publish].
Ensure the ETL pipeline is enabled.
Click the [Run] button.
The Run ETL pipeline dialog box displays. Click [Run] to execute the ETL pipeline.
The ETL pipeline will run on the date and time specified. The records will be added to the destination file.
The data will be updated from the source to destination. To verify, check the target entity. The data from the file will be updated in the target entity along with the added fields for DayMonthJoinDate, Customer_Value, and Rating.
