Specifying Records to Extract

When you specify a driver file, the system determines what to extract from other files based on the keys in the driver file. However, sometimes the values that you want to extract are not associated with a key field. For example, you have one file that contains only data that you need to reconcile. You have another file with data that you need to reconcile plus additional data that you do not want to extract at this time. The field value that determines whether to include the data is not a value that you want to use as a key for this job, so you cannot use the driver file option.

Problem

You want to extract only the values that match the values from another input source. For example, you have a corporate report that contains information for every branch within the company. You need to extract only the information that is pertinent to your department’s branches but you do not want to sort the data by branch number, so you are not including the branch number in the key.

Solution

Complete the tasks below to specify the records to extract. You will need to set up an external transaction table and define the input source.

Set Up a Translation Table

1 ) Set up one file as an external translation table.

2 ) Create a column in the external translation table for the value you want to match, for example, the branch number, and for any other values that you want to extract.

3 ) Assign a literal value of YES to an extraction variable when the columns match, and assign the values that you want to extract to other extraction variables.

Set Up an Input Source

1 ) Set up the other file as the input source.

2 ) Assign a literal value of NO to an extraction variable named BRANCH FOUND.

3 ) Set up a detail field extraction that will extract the item from the input source, translate the item, and place the item in an extraction variable named MY FIELD.

4 ) Set up a SELECT statement to check the extraction variable BRANCH FOUND, and create subsequent keys and detail fields to move the values to internal items if BRANCH FOUND = YES.

When you extract data from the input source, the system translates one value at a time, checking the BRANCH FOUND extraction variable to see if the branch is found in both files.

If the branch is found in both files, the flag is set to YES and other data that you specified in external translation table columns is extracted into the extraction variables that you set up. See step 3 under "Set Up a Translation Table." These extraction variables are then available in the input source to place into keys or internal items or for use in subsequent select statements and detail fields.

If the branch is not found, the flag value remains NO and the selection criteria fails. See step 4, above. Processing continues without this item, skipping those records for which the branch number does not match between the two files.