-
Double-click the tMap component to open
the graphical mapping editor.
-
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.
-
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.
-
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.
-
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.
-
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.
-
Drop the Age column from the main flow table to the
Expr. field of the lookup table.
-
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.
-
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.
-
At the end of the Query field, following
the Select statement, type in the following WHERE clause:
WHERE AGE
='"+((Integer)globalMap.get("people.Age"))+"'"
-
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.
-
Double-click the tMysqloutput component
to define its properties.
-
Select the Use an existing connection
check box to leverage the created DB connection.
Define the target table name and relevant DB actions.
