Using the Ternary Operator, Intermediate Variables and Reload – Talend
Using the ternary operator :
tMap expression cannot be more than a single line of Java code. This means that we cannot use the normal if-then-else logic to test for conditions.
Fortunately, Java does provide a mechanism by which we can perform tests on a single line: the ternary expression.
Using Talend Open Studio, we want to separate the given names into first_name and middle_names columns.
By using in the tMap component for an expression.
For the first name, we do this:
StringHandling.INDEX(row1.GivenNames," ") > 0 ? StringHandling.LEFT(row1.GivenNames,StringHandling.INDEX(row1.GivenNames," ")) : row1.GivenNames
And to extract the middle name/s, we use this expression:
StringHandling.INDEX(row1.GivenNames," ") > 0 ? StringHandling.RIGHT(row1.GivenNames,StringHandling.LEN(row1.GivenNames)-StringHandling.INDEX(row1.GivenNames," ")–1) : ""
This type of expression is known as a ternary operation.
The basic format of a ternary operation is this:
test condition ? do this if true : do this if false
If-Then-Else statement in Talend
If-Then-Else is the common requirement of the Talend implementation to use conditional execution of job, component or data, during various part of Talend job development.
Following are the possible ways of implementing If-Then-Else cases.
Using tMap: If Then Else with tMap
AS you know tMap is versatile component which allows us developing lookups, rejects, conditional formatting, transformation, data filter, and many more operations, therefore If-Then-Else is frequently used in tMap.
To achieve this, Talend has provided ternary operator as shown in below statement.
If you notice, we are evaluating 1==1 and if 1 equals to 1, then result is true else false. We can use the same statement in tMap when actual data lets see with actual scenario.
Lets design job for demonstration.
- Add tFixedFlowInput component and do following settings.
- In schema editor, add column “ID” with an integer type,
- Add column City and State as string
- Number of rows=10
- Select Mode=”Use Single Table”
- Add tMap after tFixedFlowInput and connect with main.
- Create output in tMap and map all the source columns to output
- Add tLogRow to see the result after tMap and connect.
Now that our sample job is ready, we will start adding various scenarios.
Scenario 1 : Process only five rows.
Type 1: Open tMap editor and click on source side expression filter and following code inside it.
row12.ID>0 && row12.ID<=5
rwo12 is my input connection name which is from tFixedFlowInput component. This code will restrict 5 rows to be processed.
Type 2: At output filter expression area, you can add same condition and achieve the same result.
See the screen for implementation.
Filters are nothing but a condition to restrict data being processed, passed or transferred for further processing.
You got how to write if at filters section which has no else part.
Scenario 2: Check the number is even or not.
Type 1: Using tMap variable
Create variable in tMap, “CheckEven” as boolean type, and write down below statement inside it.
This will check whether an incoming number is even or not.
Type 2: Inside output columns expression section.
Create another two columns at the output side “VEvenCheck” and “CEvenChack” as boolean type and assign respective values to each column as follows
Below screen shows the tMap setting with filters and if conditions.
Scenario 3: Multiple If-Then-Else statements.
We already have single If else statement, but now we are going to use multiple if else statement inside the tMap, for that we will use below sample data.
Step 1: Check test score and assign grade.
- Modify tFixedFlowInput component as follows.
- Mode=”Use Inline content(Delimited file)”
- Keep row separator default and field separator =”|”
- Add below records in the inside content area.
We are implementing below Java if then else statement in tMap
Go to the tMap and add one extra column at output as “Grade” with string type and then type below statement in it.
Above statement in tMap with result.
You can use the same statement in tMap variables section in same way. Benefit of using a variable is you can use the same variable in multiple output columns, to avoid replicating same calculations.
Below is the list of components that supports normal If-Then-Else statements.
- Talend Routines
Using intermediate variables in tMap
The Map — tMap in Talend Open Studio — is the workhorse of data conversion and ETL processing. Occasionally, you’ll need to work with a variable or expression. There are several places in Talend where you can put an expression.
Maps are a productive way to transfer the fields of a source to a target using a graphical tool. When there’s a one-to-one correspondence between source and target fields, the map can be created with a simple drag-and-drop. However, sometimes the map may require some logic or special processing in certain fields.
Examples of this special processing include:
Specifying a default value
In tMap, you can put these variables in three places:
- In the target field Expression,
- As a Var, or
- In a tSetGlobalVar
The target field and Var panel locate the variable within the tMap component. tSetGlobalVar locates the variable and expression out of the tMap component.
IN THE TARGET FIELD
Writing expressions in the target field is a concise way to process a field. The target field Expression features an Expression Builder.
AS A VAR
If your expression needs to be used throughout the map’s fields, you can create a variable in the same window.
Notice that the variable is preceded with ‘Var’.
This step involves adding another component to the canvas. In addition to tMap, add a tSetGlobalVar component. Rather than connecting the input Excel connection to the tMap, put the tSetGlobalVar in between. This will require re-mapping the source and target fields; use the row of the tSetGlobalVar rather than the Excel file.
Double-click on the tSetGlobalVar and add a variable. Make sure the variable is surrounded with quotes. Here, the example uses “mystate” and it is set with an expression that handles the null. In the tMap, you’ll use the following syntax in the target field. The variable is stored in a Java map. Don’t forget the cast.
There is a lot of flexibility in setting variables in Talend Open Studio. Pick the most concise syntax that gives you access to the variable where you need it.