Skip to main content Skip to complementary content

Editing individual expressions

About this task

The following example shows how to use the Expression Builder to edit two individual expressions.

Example of a Job using the tMap, tMysqlInput, tFileInputDelimited, and tLogRow components.

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.

Procedure

  1. In the tMap, set the relevant inner join to set the reference mapping.
    For more information regarding tMap, see tMap operation and Mapping data flows.
  2. 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.
  3. Click in the first Expression field (row1.Name), and then click the ... button that appears next to the expression.
    Location of the '…' button.

    The Expression Builder dialog box opens up.

    Overview of the Expression Builder dialog box.
  4. In the Category area, select the relevant action you want to perform. In this example, select StringHandling and select the EREPLACE function.
  5. 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
  6. Now check that the output is correct, by typing in the relevant Value field of the Test area, a placeholder value, e.g: Chuck Norris and clicking Test!. The correct change should be carried out, for example, Chuck_Norris.
  7. Click OK to validate the changes, and then proceed with the same operation for the second column (State).
  8. In the tMap output, select the row2.State Expression and click the [...] button to open the Expression builder again.
    Overview of the Expression Builder dialog box.

    This time, the StringHandling function to be used is UPCASE. The complete expression says: StringHandling.UPCASE(row2.State).

  9. Once again, check that the expression syntax is correct using a placeholder 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.

    Map editor.

Results

These changes will be carried out along the flow processing. The output of this example is as shown below.

Example of some results.

Did this page help you?

If you find any issues with this page or its content – a typo, a missing step, or a technical error – please let us know!

Leave your feedback here