Merging Data Sources Using the Match Wizard

How to:


You can merge two or more data sources, and specify which records to merge and which to sort out, using the Match Wizard. The wizard creates a new data source (a HOLD file), into which it merges fields from the selected records. You can report from the new data source and use it as you would use any other HOLD file. However, you cannot specify a format for the HOLD file. It will be created as a BINARY or ALPHA HOLD file depending on the value of the HOLDFORMAT parameter. The merge process does not change the original data sources.

You select the records to be merged into the new data source by specifying sort fields in the Match Wizard. You specify one set of sort fields (using the BY phrase), for the first data source, and a second set of sort fields for the second data source. The Match Wizard compares all sort fields that have been specified in common for both data sources, and then merges all records from the first data source whose sort values match those in the second data source into the new HOLD file. You can specify up to 32 sort sets. This includes the number of common sort fields.

In addition to merging data source records that share values, you can merge records based on other relationships. For example, you can merge all records in each data source whose sort values are not matched in the other data source. Yet another type of merge combines all records from the first data source with any matching records from the second data source.

You can merge up to 16 sets of data in one Match request. For example, you can merge different data sources, or data from the same data source.

Note: The limit of 16 applies to the most complex request. Simpler requests may be able to merge more data sources.

Access the Match Wizard from the Procedure Viewer in Developer Studio. It uses typical wizard behavior so you can easily navigate from one step in the process to the next.

Top of page

Procedure: How to Use the Match Wizard
  1. In the Procedure Viewer, click a component connector (yellow diamond).

    The Component Connector toolbar opens.

  2. On the Component Connector toolbar, click Match .

    The Open dialog box appears.

  3. Select the first Master File to use in the MATCH logical expression, as shown in the following image.

  4. Click Open.

    The Match Wizard opens, as shown in the following image. It uses typical wizard behavior in which you click a series of Next buttons to navigate from one window to the next. You can also use the Match Preview area to navigate the Wizard.

  5. From the Available fields list, select the fields you want to use to build the Match request.

    You can add a Match Alias (how the field appears in the Hold file field list) and a Prefix Operator for each field.

    Note: You can remove fields from this list by clicking the Delete button. If you add more than one field, you can also use the Move Up and Move Down buttons to rearrange the order of the fields.

  6. Select either PRINT, SUM, or LIST from the drop-down list.
  7. To add a field as a BY field, click a field in the Available Fields list and click the Add field button in the BY fields list box.

    Note: You must have at least one BY field.

  8. Click the Where/If tab if you want to create a WHERE expression or IF condition. This limits the records that are merged together.
  9. Click the Add WHERE expression or Add IF condition button to open the Expression Builder. Use the Expression Builder to create the Where/If condition.
  10. Click Next. Step 3 of the wizard opens, as shown in the following image.

  11. Click the Browse button to select the data source you want to merge with the original data source.

    The Open dialog box opens.

  12. Select a Master File and click Open.
  13. Click Next.

    Step 4 of the wizard opens.

  14. Add the fields for the second data source, and Where/If criteria, as in Step 5.

    Note: In the following image, the second data source is CENTCOMP and the field added is STATE.

  15. Click Next.

    Step 5 of the Match wizard opens, as shown in the following image.

  16. Type a name for the Hold file and select the type of match phrase.
  17. Click Finish.

    In the following image, the Match Wizard - Summary window opens where you can review the final Match request.

  18. If you:
    • Have completed your match request, select the Create a Report, Create a Graph, or the Neither option and click Close. Optionally, click the Save the HOLD file to a permanent location check box. When you select this option, the HOLD file is saved to the current Project directory, as shown in this image.

      If you select the Create a Report, Create a Graph, or Neither option, Report Painter, InfoAssist, or Procedure Viewer opens, respectively.

    • Want to make more changes to the Match request, click Back or use the Match Preview area. See How to Edit a Match Request.

Top of page

Procedure: How to Edit a Match Request
  1. Open the Match request.
  2. In the Match Preview area, click on the file or option you want to edit. The appropriate step in the Match Wizard opens in the left pane.
  3. Make your changes and exit the Wizard.

Top of page

Reference: Usage Notes for Match Requests