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.

If you would like to Enrich your career with a Talend certified professional, then visit Mindmajix - A Global online training platform: “Talend Certification Course” . This course will help you to achieve excellence in this domain.

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

---------          Related Article: Talend vs Informatica big data?          -------

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.

result= 1==1?true:false;

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”

ID=Numeric.sequence(“s1”,1,1)
City=TalendDataGenerator.getUsCity()
State=TalendDataGenerator.getUsState()

• 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.

Frequently Asked TALEND Interview Questions & Answers

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.

row12.ID%2==0?true:false

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

CEvenCheck=row12.ID%2==0?true:false
VEvenCheck=Var.CheckEven

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

Java

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.

Learn more about Java in Talend: Using Java In Talend

Below is the list of components that supports normal If-Then-Else statements.

• tJava
• tJavaRow
• tJavaFlex
• 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.

--------         Related Page: Managing Talend Context Variables        ------

Examples of this special processing include:

Null handling

(row1.State==null)?””:row1.State

Specifying a default value

(row2.New_Region==null)?row1.Region:row2.New_Region 

In tMap, you can put these variables in three places:

1. In the target field Expression,
2. As a Var, or
3. 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’.

USING TSETGLOBALVAR

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.

(String)globalMap.get("mystate")

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.