All expressions (Input, Var or Output) and constraint statements can be viewed and edited directly in the expression fields, in the expression editor, and in the Expression Builder.
The expression editor provides visual comfort to write any function or transformation in a handy dedicated view.
You can write the expressions necessary for the data transformation directly in the Expression editor view located in the lower half of the expression editor.
To open the Expression editor view, complete the following:
Double-click the tMap component in your Job design to open the Map Editor.
In the lower half of the editor, click the Expression editor tab to open the corresponding view.
To edit an expression, select it in the Input panel and then click the Expression editor tab and modify the expression as required.
Enter the Java code according to your needs. The corresponding expression in the output panel is synchronized.
Refer to the Java documentation for more information regarding functions and operations.
Some Jobs require pieces of code to be written in order to provide components with parameters. In the Component view of some components, an Expression Builder interface can help you write such pieces of code (in Java), known as expressions.
Using the Expression Builder of tMap, you can edit the expression for an input column, an output column, or a variable, or change the expressions for multiple output columns at the same time.
The following example shows how to use the Expression Builder to edit two indiviual expressions.
In this example, two input flows are connected to the tMap component.
From the DB input, comes a list of names made of a first name and a last name separated by a space char.
From the File input, comes a list of US states, in lower case.
In the tMap, use the expression builder to: First, replace the blank char separating the first and last names with an underscore char, and second, change the states from lower case to upper case.
From the main (row1) input, drop the Names column to the output area, and the State column from the lookup (row2) input towards the same output area.
Click in the first Expression field (row1.Name), and then click the [...] button that appears next to the expression.
The [Expression Builder] dialog box opens up.
In the Category area, select the relevant action you want to perform. In this example, select
StringHandlingand select the
In the Expression area, paste row1.Name in place of the text expression, in order to get:
StringHandling.EREPLACE(row1.Name," ","_"). This expression will replace the separating space char with an underscore char in the char string given.
Note that the CHANGE and EREPLACE functions in the StringHandling category are used to substitute all substrings that match the given regular expression in the given old string with the given replacement and returns a new string. Their three parameters are:
oldStr: the old string.
newStr: the regular expression to match.
replacement: the string to be substituted for every match.
Now check that the output is correct, by typing in the relevant Value field of the Test area, a dummy value, e.g: Chuck Norris and clicking Test!. The correct change should be carried out, for example, Chuck_Norris.
Click OK to validate the changes, and then proceed with the same operation for the second column (State).
In the tMap output, select the row2.State Expression and click the [...] button to open the Expression builder again.
This time, the
StringHandlingfunction to be used is
UPCASE. The complete expression says:
Once again, check that the expression syntax is correct using a dummy Value in the Test area, for example indiana. The Test! result should display INDIANA for this example. Then, click OK to validate the changes.
Both expressions are now displayed in the tMap Expression field.
These changes will be carried out along the flow processing. The output of this example is as shown below.
tMap allows you to define the transformation behavior for multiple output columns at the same time.
Using a simple transformation Job, the following example shows how to define expressions on multiple columns in a batch manner in tMap.
Here is the content of the input CSV file used in this example:
id;firstname;lastname;city;state 1; Andrew;Adams;Madison; Rhode Island 2;Andrew; Garfield; Saint Paul;Colorado 3; Woodrow; Eisenhower ; Juneau; New Hampshire 4;Woodrow; Jackson;Denver;Maine 5; Lyndon;Buchanan; Pierre; Kentucky 6; Bill;Tyler; Helena; New York 7;George;Adams;Oklahoma City ;Alaska 8;Ulysses; Garfield;Santa Fe;Massachusetts 9; Thomas;Coolidge ;Charleston; Mississippi 10;John;Polk; Carson City; Louisiana
In this example, all the output columns of type String will be trimmed to remove preceding and training whitespace and the last names and state names will be transformed to upper case.
In the Map Editor, complete the input-output mappings.
Select the columns of type String in the output table, namely firstname, lastname, city, and state in this example, and right-click the selection so that the Apply Routine button shows up.
Click the Apply Routine button to open the [Expression Builder] dialog box.