Checking a Column against a list and lookup in Talend

Talend Open Studio for Data Quality enables you to analyze the content of one or multiple columns and execute the created analyses using the Java or the SQL engine. This type of analysis provides statistics about the values within each column.

If you would like to Enrich your career with a Talend certified professional, then visit Mindmajix - A Global online training platform: “Talend Online Course” . This course will help you to achieve excellence in this domain.

Checking a Column Against a List of Allowed Values

Often it is necessary to ensure that a column contains only values as defined in a list. This recipe shows how this can be achieved using a tMap expression.

Getting ready

Open the job jo_cook_ch03_0040 _tMapValuesInList. You will notice that the job is very similar to  REJECTING ROWS USING TMAP.

How to accomplish it…

1. Open tMap and click the expression builder button (…), and add the test criteria, as shown in the following screenshot:

2. Run the job and you should see that one of the rows is rejected.

How it works…

The tMap conditions are the same as Java conditions, so the symbol || (pipe pipe) is a logical OR. Thus, the condition checks for the value being UK or USA or France.

There’s more…

This method is fine if the list is quite small and isn’t liable to change. If the list is too large or subjected to frequent changes, then the code will be hard to maintain and/or will need to be changed often, which requires re-testing of the code.

Frequently Asked TALEND Interview Questions & Answers

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 field countryOfBirth from the customer input to the countryName field 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 tMap and change the key on the country to StringHandling.UPCASE(customer.countryOfBirth)

• Re-run the job, and you will see that now only one record has been rejected.

Checkout Talend Tutorial

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.