Configuring the inner join

Double-click the Resellers component to display its Basic settings view.

Select Repository from the Property type list and select the component's schema, resellers in this scenario, from the Repository Content dialog box. The rest fields are automatically filled.

Note:

In this scenario, the input schemas are stored in the Metadata node of the Repository tree view for easy retrieval. For further information regarding metadata creation in the Repository, see Talend Studio User Guide.

Double-click the tMap component to open the Map Editor.

Note that the schema of the new input component is already added in the Input area.

Create a join between the main input flow and the new input flow by dropping the ID_Reseller column of the Cars_data table to the ID_Reseller column of the Resellers_data table.

Click the tMap settings button at the top of the Resellers_data table and set Join Model to Inner Join.

Drag all the columns except ID_Reseller of the Resellers_data table to the main output table, Insured.

Note:

When two inner joins are defined, you either need to define two different inner join reject tables to differentiate the two rejections or, if there is only one inner join reject output, both inner join rejections will be stored in the same output.

Click the [+] button at the top of the output area to add a new output table, and name this new output table Reject_ResellerID.

Drag all the columns of the Cars_data table to the Reject_ResellerID table.

Click the tMap settings button and select Catch lookup inner join reject to true to define this new output table as an inner join reject output.

If the defined inner join cannot be established, the information about the relevant cars will be gathered through this output flow.

Now apply filters on the two Inner Join reject outputs, in order for to distinguish the two types of rejection.

In the first Inner Join output table, Reject_OwnerID, click the plus arrow button to add a filter line and fill it with the following formula to gather only owner ID related rejection: Owners_data.ID_Owner==null

In the second Inner Join output table, Reject_ResellerID, repeat the same operation using the following formula: Resellers_data.ID_Reseller==null

Click OK to validate the map settings and close the Mapper Editor.

Double-click the No_Reseller_ID component to display its Basic settings view.

Specify the output file path and select the Include Header check box, and leave the other parameters as they are.

To demonstrate the work of the Mapper, in this example, remove reseller IDs 5 and 8 from the input file Resellers.csv.

Configuring the inner join - 7.3

tMap

Procedure