The most typical use of Merge/Purge processing is when you have a file of existing customers and also an external list of prospective customers. Typically, you want to write to all the new prospects, but want to be sure you do not inadvertently send the same letter to any of your existing customers. In this case, you need to locate any of your existing customers that happen to be on the new list, and "purge" them from it.
If, on the other hand, you do want to send the same letter to all existing customers and prospects as well, what you can do is to create a new file (or table) by "merging" the new list and your file of existing customers, eliminating (or not creating), any duplication. These two operations have much in common, and in fact when merging, mDesktop first purges records from the new file and then merges the remainder into the Main File. Thus purge is the same as merge, but stopping short of the final stage; no merging takes place.
Each input file must have been Imported into a mDesktop Main File (using the Setup Wizard or Import records options) before you can do Find Overlap successfully.
You do not have to Find and Delete Matches from each individual file before running Find Overlap, but you must do so if you want the internal duplicates in each file removed!
The Basic Merge/Purge Process is as follows:
- Find Overlap.
- View/Verify the Overlap to check the matches found.
- Merge or Purge (for a suitable threshold score).
|
Note |
You do not have to Find and Delete Matches from each individual file before running Find Overlap, but you must do so if you want the internal duplicates in each file removed! |
File Layouts when Merging Files
Before merging two files, the two Main File Layouts should be checked to pre-empt problems later on e.g. has one file got more address lines or wider fields? If so, make sure that the Main File is the one with more lines or the wider fields. If necessary, you should use Main File Layout from the Jobs/Setup menu to add or widen fields in the Main File.
If you are using the Multiple File Wizard to merge files, the wizard creates a master layout for you which preserves all of the data from the individual files i.e. it is a superset of all the individual structures.
The Main File is the first file you select. Purge Records does not change the Main File, but removes records from the Second File. Merge Files adds unique records from the Second File to the Main File.
Unique References
If you let mDesktop allocate Unique References when the files were originally imported, you must make sure that the Unique References don't overlap between the files. To ensure this, you can set the Next Reference Number (in Matching Options) before Importing the second file, to a value at least greater than the number of records in the first file. Alternatively, you can set it to zero if you want mDesktop to prompt you for the starting Unique Reference whenever you Import a file. If your Unique References are not unique between the two files, you can use Generate Unique References in the Fields menu in Tools/Database Utilities to regenerate them in either file or in the merged file.
If you are using the Multiple File Wizard, the wizard does this for you.
Find Overlap matches the records from one Main File against records with the same key from another file. You can then view a report of the results and choose which records to merge or purge, individually or by a range of the scores. If you have several files to merge, you must merge each one at a time into the Main File, use Job Scripts or the Multiple File Wizard. If you want to remove internal duplicates in the files being merged, you must find and delete matches from each file individually, before running Find Overlap on the two files.
If you are using the Multiple File Wizard, the wizard does all this for you.
|
Find Overlap |
Obviously, to Find Overlap, two files must be open. If this is not the case, mDesktop will prompt you for file names when "Find Overlap" is selected (from the toolbar or Merge/Purge menu). |
The "Matching Key and Range" screen is shown first, as for Find Matches. Choosing the keys for finding the overlap between two files is exactly the same as for finding matches within one file, except that the Match Keys displayed will be limited to fields which are in both files and have the same width. If for example, Zip is not displayed as a match key, it may well be because it has a different width in the second file from the first. To overcome this problem, you can type in a match key in the Index box of e.g. LEFT(ZIP,5). See "Match Key – Definition" to review the description of entering your own keys.
After mDesktop has done its work and found any matches, a summary is displayed (in the usual report preview screen) and then the screen below, which offers the most common options:
The choices from here are:
- View Overlap between the two files (similar to View Matches)
- Verify Overlap between the two files (similar to Verify Matches)
- Merge Files into the Main File
- Flag Records from the Second File
- Cancel
If you are selecting any of these options having previously closed your two files, ensure they are reopened in the same order as before. If you open them the wrong way round, mDesktop prompts you to swap them, to ensure that it shows or processes the right pairs of matches.
As for Flag Records, when you select any option for processing the results of Find Overlap from the Merge/Purge menu, it allows you to view the individual match scores or score bands, before proceeding to the operation that you selected.
Overlap Summary
This is essentially the same report as for matching: see "Matching Summary".
View/Verify Overlap
Viewing and verifying the overlap between two files is similar to viewing and verifying matches within a single file (see View/Verify Matches). The only difference is that different reports are used and you can only View Overlap in pairs, not sets.
View Overlap
View Overlap can be selected from the dialog presented after finding the overlap between two files or from the Merge/Purge menu.
Verify Overlap
As with Verify Matches, this can be selected straight after finding the overlap or from the Merge/Purge menu.
The screen displayed is the same as that for Verify Matches. For details see "Verify Matches". If the two files have different layouts, the fields displayed for each record may be different. You can use the Change Fields button to change the order in which the fields are displayed, to help you compare the two records more easily.
When selecting Merge Files, you can enter the highest match score which you want to merge records for. If you enter zero, only those records on the Second File which were not given a Matching Score will be merged into the Main File. If you enter 100, only those records on the Second File which were given a Matching Score less than 100 in the MERGES table will be merged. If you enter all nines, or if the MERGES table is empty, all records on the Second File will be merged.
If you select Flag Records, you can enter the lowest score which you want to purge records for. If you enter zero, which gives the highest possible number of deletions from the Second File, all those records on the Second File which were given a Matching Score in the MERGES table will be purged from the Second File. If you enter 100, all records on the Second File which were given a Matching Score greater than or equal to 100 in the MERGES table will be purged. If you enter all nines, or if the MERGES table is empty, no records on the Second File will be purged. Finally, the Second File is selected as the Main File in use, to enable you to go straight to the Output menu without having to remember to select the right file first.
This option in the Merge/Purge menu allows you to transfer data (such as telephone numbers, company data and geodemographic codes) from a master file to a customer file. You must run Find Overlap first.
The steps for this process are:
- Open your Main File and your Second File and run "Find Overlap" (from the Merge/Purge menu).
- Select "Transfer Data" (also from the Merge/Purge menu).
- Modify matching scores or click "Continue" (click Continue if unsure).
- The following screen will appear:
Data can be transferred between any fields which the Files have in common and these are shown in the left-hand panel. If you wish to transfer data from a field not listed, you must first use "Main File Layout" to add fields as necessary.
Either double-click or single-click and select "Move" or "Remove" to move or remove fields from the available list to selected field list or back.
Below the field picker there are other options can be set:
- Transfer for scores – data is only transferred for records whose matching score is equal to or greater than the value shown.
- Transfer data – from Main to Second shows where the data will be copied from to. Clicking on one of the buttons (main/second) will swap the order.
- Always Replace/Only if Empty – If there is data in a field that is about to be overwritten, mDesktop needs to know whether to replace it (always replace) or leave it (replace only if empty).
- Blank All Fields/Append to Existing Fields – You can optionally choose to blank all the fields you are transferring to before starting, or append the data being transferred to that already in the field.
- In all cases, you should ensure that the field being transferred to is large enough to contain all the data or some of it will be lost. To make sure that only the best match is used to transfer data across where many records match one, define a field MATCHSCORE (numeric 8) in your receiving file, using "Main File Layout".
- Click "Begin Transfer" to start the process.
- mDesktop will select this new table as the current one and a summary screen will be displayed.
This option on the Merge/Purge menu allows you to write overlapping records (after Find Overlap) from the Main File, to a new file e.g. to mail multiple buyers.
The steps for this process are:
- Open your Main File and your Second File and run "Find Overlap" (from the Merge/Purge menu).
- Select "Write Overlapping Records" (also from the Merge/Purge menu).
- Modify matching scores or click "Continue" (click Continue if unsure).
- A further window will appear, from which you select the minimum score for writing out the records. The default (as for Find Overlap) is to output records with a matching score greater than or equal to the Minimum score to report. Click "Continue" to continue.
- You will then see window that allows you to either output the "Overlapping Records" or to access the "QA Dashboard". Select the "Overlapping Records" option and you will see a new window that will allow you to specify your new files layout.
- Select Generate Output, once your options have been specified, to begin the operation. You will now be prompted for a file name and location.
- mDesktop will select this new File as the current Main File and a summary screen will be displayed so you can go on to produce output or return to the main menu (the Done button).