Skip to main content Skip to complementary content

Configuring the components

Procedure

  1. Double-click the tMap component to open the graphical mapping editor.
  2. The Output table (that was created automatically when you linked the tMap to the tMySQLOutput will be formed by the matching rows from the lookup flow (large_data_volume) and the main flow (people_age).
    Select the main flow rows that are to be passed on to the output and drag them over to paste them in the Output table (to the right hand side of the mapping editor).
    In this example, the selection from the main flow include the following fields: id, first_name, last_Name and age.
    From the lookup table, the following column is selected: city.
    Drop the selected columns from the input tables (people and large_volume) to the output table.
  3. Now set up the join between the main and lookup flows.
    Select the age column of the main flow table (on top) and drag it towards the age column of the lookup flow table (large_volume in this example).
    A key icon appears next to the linked expression on the lookup table. The join is now established.
  4. Click the tMap settings button, click the [...] button corresponding to Lookup Model, and select the Reload at each row option from the Options dialog box in order to reload the lookup for each row being processed.
  5. In the same way, set Match Model to All matches in the Lookup table, in order to gather all instances of age matches in the output flow.
  6. Now implement the filtering, based on the age column, in the Lookup table. The GlobalMapKey field is automatically created when you selected the Reload at each row option. Indeed you can use this expression to dynamically filter the reference data in order to load only the relevant information when joining with the main flow.
    As mentioned in the introduction of the scenario, the main flow data contains only people whose age is either 40 or 60. To avoid the pain of loading all lookup rows, including ages that are different from 40 and 60, you can use the main flow age as global variable to feed the lookup filtering.
  7. Drop the Age column from the main flow table to the Expr. field of the lookup table.
  8. Then in the globalMap Key field, put in the variable name, using the expression. In this example, it reads: "people.Age"
    Click OK to save the mapping setting and go back to the design workspace.
  9. To finalize the implementation of the dynamic filtering of the lookup flow, you need now to add a WHERE clause in the query of the database input.
  10. At the end of the Query field, following the Select statement, type in the following WHERE clause: WHERE AGE ='"+((Integer)globalMap.get("people.Age"))+"'"
  11. Make sure that the type corresponds to the column used as variable. In this use case, Age is of Integer type. And use the variable the way you set in the globalMap key field of the map editor.
  12. Double-click the tMysqloutput component to define its properties.
  13. Select the Use an existing connection check box to leverage the created DB connection.
    Define the target table name and relevant DB actions.

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!