This option allows you to merge records from one file to another based on matching keys. The primary and secondary files must be sorted in ascending order by key value. Use fixed file combine/sort to prepare your data to merge.
The Merge fixed length data based on matching keys dialog box is split into 4 sections:
Primary file
Secondary file
Output file
Save as opens a dialog box that allows you to specify the output file name. You can also specify the file name by entering the full path name into the Output file field.
Merge options and filters
There are 4 options for merging files:
Merge secondary record to the end of each primary record
When keys match, the secondary record is appended to the end of the primary record and written to the output file.
Merge secondary record starting at primary position <start>
When keys match, the entire secondary record is merged into the primary record starting at the position specified. This position can be any existing position in the primary record, or a position beyond the records current ending position. When merging a record into the primary file the data is overwritten with the secondary data. If the merge position is past the end of the primary record, spaces will be added from the end of the record to the merge position where the secondary record starts.
Merge secondary position <start> to <end> into primary position <start>
Allows for the merging of secondary data into the primary record at a specified start position.
When this option is selected, enter the starting and ending field positions of the secondary file and the starting position in the primary file.
The primary position can be any existing position in the primary record, or a position beyond the records current end position. When merged into the primary record, data is overwritten with the secondary data. If the merge position is past the end of the primary record, spaces will be added from the end of the record to the new merge position.
Load merge positions from file
When you select this option you are prompted for a merge file to use. The file is then loaded and displays the secondary and primary field locations to be merged. This allows for multiple locations to be processed at one time.
This is a 3 field flat ASCII file created on any text editor in the following format:
<Secondary start> - <Secondary end> , <Primary start>
Example file
1-5,10
11-11,20
21-30,22
When processed, this file will perform the following operations:
Take secondary positions 1 through 5 and put into primary position 10 to 14
Take secondary position 11 and put into primary position 20
Finishing with secondary position 21-30 and put into primary position 22-31
Merge filters and counters
There are 3 filters available to help control what is passed to the output file. Each filter has a counter that displays the number of records matching the filter. These numbers are updated on each merge.
To select the filter, check the box associated with the filter:
Merge if primary and secondary keys match
Merge records in the primary file that have a matching key in the secondary file. This includes any duplicated keys in the primary file.
Allow duplicate primary keys
Merge records in the primary file that are duplicated. Check this filter to allow the output of duplicate keys in the primary file.
Output primary when secondary not found
These records have key values that could not be found in the secondary file. If you want to include these records in the output file they will be copied unchanged to the output file.
If you uncheck all filters nothing will be written to the output file, but all counters will be updated with statistics from the files processed.
Run
Selecting the Run button will check for any entry errors and process the primary and secondary input files creating a new output file. When processing is finished your entries will remain intact. This allows for changes and the process easily repeated.
Close
Selecting the Close button will clear all fields and return to the WCSortMerge dialog box.