Checking a column against a lookup
Another method for validating a column is to refer to a lookup containing a list of allowed values that can be stored in any format (file, table, XML for example).
Getting ready
Open the job jo_cook_ch03_0050_tMapValuesInLookup.You will see that there are two inputs to the tMap: customer and country.
How to do it…
- Open
tMap, and drag the fieldcountryOfBirthfrom the customer input to thecountryNamefield in the country input. This will create a key link, as shown in the following screenshot:
- Click the button tMap settings and set the value for Join Model to
Inner Join.
- In the reject output, click on the button for tMap settings, and set the value for Catch lookup inner join reject to
true.
- Run the job and you will see that three of the records have been rejected.
- Re-open the
tMapand change the Expr.key on the country toStringHandling.UPCASE(customer.countryOfBirth) - Re-run the job and you will see that now only one record has been rejected.
How it works…
The tMap is performing an inner join between the customer data and the country data using the country name as the key, so any rows that do not join have an invalid countryOfBirth.
When a match is found, the record is passed to the valid rows output.
If no match is found, then the customer record is passed to the invalid output, which is set up to catch any row from the main flow that does not fulfill the inner join criteria.
On the first execution of the job, the values being checked were not in upper case, so only 'USA' matched. On the second execution after the customer countries had been converted to upper case, three of the records matched.