Comparing Values to a Lookup File

You can create a filter that compares the values in a source file to the values in a lookup file. The filter has the value 1 if the value from the source file is found in the lookup file, and the value 0 (zero) if the value from the source file is not found in the lookup file.

From the filter pane drop-down menu, point to Lookup other file and click Check: has value.

The Select Lookup Synonym dialog box opens, as shown in the following image.

The top of the dialog box has check boxes for conditions for automatically matching to the lookup field. By default, foreign-to-primary key match and matching names are selected. You can change the selections.

Once you have made your selections, select an application directory. The synonyms with fields that match your selections display in the right pane, as shown in the following image.

Select the synonym you want to use. The DB_INFILE dialog box opens. If it doesn't automatically find a match, you can select the matching fields. However, if it could find a match using the methods checked at the top of the Select Lookup Synonym dialog box, the source field and lookup field are listed, as shown in the following image.

When you are finished, click OK.

To see the filter that was created, right-click the filter name in the Tables and Columns pane, and click Properties. The DB_INFILE calculation is displayed in the Expression text box, as shown in the following image.

If you want to change the lookup properties, you can edit the expression, and click Apply.