tMap expression syntax
This article introduces the syntax to use in the Expression Builder, to help you understand and use the tMap component.
tMap is one of the core components of Talend Studio and is used very often in Jobs. The tMap component is primarily used for mapping input fields to output fields and transforming the input data in the Expression Builder of the corresponding output column.
The Expression Builder allows you to type in a mapping expression for each column in each output table. This article presents the following syntax examples:
Basic mapping
The tMap provides a graphical tool called Map Editor that allows you to create the mapping more easily from the input table to the output table using a simple drag-and-drop action. The following screenshot shows a simple mapping without any transformation from the input table to the output table.
In the screenshot above, you can see the format used to map the input column in the Expression Builder of the corresponding output column is: rowName.columnName, row1.customer_id in this example. row1 is the row identifier of the input table. It is not a constant, and it may change according to your Job. You can always read it at the top of each input table.
Concatenating fields
You can concatenate multiple fields using the + character in the Expression Builder. As long as the type of at least one of the fields is string, the multiple strings are connected, as shown below.
Setting output column with a constant
You can set a constant to an output column in the Expression Builder, without mapping from the input columns. As shown below, the company column of type String is set to a fixed value, Talend.
The input data will be transformed to output data after being processed into the tMap component.
Input table:
employee_id | name |
1 | Elisa |
2 | Nicolas |
3 | Patrick |
Output table:
employee_id | name | company |
1 | Elisa | Talend |
2 | Nicolas | Talend |
3 | Patrick | Talend |
The data type of the value in the Expression Builder should be consistent with the data type
of the output column. Otherwise, it will throw a Java compilation error. For example, if a
constant 1 is set for company column in the Expression Builder, it will throw a compilation
error when the Job is executed: Type mismatch: cannot convert from int to
String
, because the data type of the company column is defined as type String, but
an Integer/int value is set for this column.
Outputting blank values as null or fixed values
You can output blank values as null or fixed values using expressions.
To output blank values of a column as null, use the following expression syntax:
rowName.columnName.equals("")?null:rowName.columnName
To output blank values of a column as fixed values, use the following expression syntax:
rowName.columnName.equals("")?fixValue:rowName.columnName
In the following example, any blank values in the name
column will be
replaced with null; any blank values in the company
column will be replaced
with the string Unknown.
The following two tables show the input and output of a tMap component with these two expressions applied.
Input table:
id | name | company |
---|---|---|
1 | John | Talend |
2 | Â | Apple |
3 | Jane | Â |
Output table:
id | name | company |
---|---|---|
1 | John | Talend |
2 | null | Apple |
3 | Jane | Unknown |
Arithmetic operation
You can perform arithmetic operations including additions, subtractions, multiplications and divisions in the Expression Builder.
As shown below, there is a new column called new_salary in the output table. Its value is the original salary plus 500.
The input data will be transformed to output data after being processed in the tMap component.
Input table:
employee_id | name | salary |
1 | Elisa | 12000 |
2 | Nicolas | 13000 |
3 | Patrick | 10000 |
Output table:
employee_id | name | salary | new_salary |
1 | Elisa | 12000 | 12500 |
2 | Nicolas | 13000 | 13500 |
3 | Patrick | 10000 | 10500 |
Calling a Java class method
You can call a Java class method to transform the input fields in the Expression Builder. For exmaple, you can call a Java method to cut the first 4 characters from the input field of long type, and convert the result to a value of Integer/int type.
The input data will be transformed to output data after being processed in the tMap component.
Input table:
customer_id | account_num |
1 | 87462024688 |
2 | 87514054179 |
Output table:
customer_id | account_num |
1 | 8746 |
2 | 8751 |
Calling a Talend Routine
You can call a Talend Routine in the Expression Builder. You can call a system routine
function Numeric.sequence("s1",1,1)
to generate a sequence number for each
row.
If/else statement
In the Expression Builder, the if / else statement can be written as follows:
condition?value if true:value if false
You can perform a null test before calling a Java method; otherwise it will throw a null pointer exception if the input data contains a null value. For example:
row1.Postal_code==null?null:row1.Postal_code.toUpperCase()
You can also write a nested statement. For example, use the following expression to retrieve the new salary for each employee based on his/her original salary.
row1.salary>=10000?row1.salary*1.05:(row1.salary>=8000?row1.salary*1.1:(row1.salary>=7000?row1.salary*1.08:row1.salary*1.2))
Filter expressions
In the tMap component, you can click the filter button and write a filter expression to filter the output data, as shown below:
If there are multiple conditions, you need to use the logical operators (AND, OR) to combine multiple conditions. For example:
AND operator: all conditions should be satisfied.
row1.postal_code!=null&&row1.gender.equals("F")
In this example, the postal_code column should not be null AND the gender should be F.
OR operator: one condition should be satisfied.
row1.country.equals("CN")||row1.country.equals("FR")||row1.country.equals("US")
In this example, the country column should be one of CN, FR or US.