In my last blog about merging duplicate records we looked at merging duplicate records on just one table. I get asked many times about what the quickest method into merging between two tables, so I thought it might be good idea to explain how we go about merging between two tables, again we use our Clean & Match 2012 to demonstrate this.
So, lets assume we have our marketing list (we shall call this our Master list) and would like to match it against another list to try and extract more details to enhance our marketing list. For example, on our Master list we do not have any postcodes, but there are postcodes on the 2nd list. Lets look at adding the postcodes from the 2nd list into our master list.
The first thing we need to do is to import the two lists into Clean & Match. We can import virtually any type of list (excel, access, csv/text files, sql server and even Outlook Contacts). We could, for example, import an Excel spreadsheet and an Access database, then export the cleansed and merged list into SQL Server!, its thats flexible!
So, lets import our master marketing list into Table 1 and the 2nd list into Table 2. Just like when we merged duplicates on one table, we firstly need to perform a deduplication (dedupe) on the two lists. We can then look at how easy it is when merging between two tables.
This is how the two lists look after import. The top list is our master list (with no postcodes) and the 2nd list is our mailing list with postcodes.
We now need to match the two lists, and from these matches we can then merge the postcodes from the 2nd list.
To do this, all we simply need to do is the following:
- Perform a match against the two lists
- Select which table is the master
- Perform a merge on just the ‘Postcode’ column
Lets take a look more closely at each step.
1. Perform a match against the two lists
Click the ‘Match’ section on Clean & Match then to match Table 1 against Table 2. Choose the column you wish to use for matching then select the fuzzy matching option. In this example we choose 75% threshold and it found many matches.
As you can see below on one of the duplicates, its matching one of the records from our master file to one of the records on the 2nd file that has a postcode.
2. Select which table is the master
So, at this stage, we need to tell Clean & Match which of the tables is our master file. We simply click the ‘Set Master Table’ and select “Table 1”, as shown below.
3. Perform a merge on just the ‘Postcode’ column
We simply click the “Merge/Update” button and select the “Partial Update” as shown below.
We now just need to tell Clean & Match which column to merge. Click the “Select Columns” and select the ‘Postcode‘ column on both Table 1 and Table 2.
Click the ‘Accept” button, then click “Execute“, it will now merge the postcodes from table 1 onto table 2, for all the records that match. Merging between two tables is now complete!
Lets go back to the DATA tab to view the two tables now, we can see that our master table (table 1) contains postcodes!
WinPure Clean & Match provides an array of powerful merge functions including this easy method of merging between two tables, that when used with its powerful deduplication engine provides exceptional results within a very short period of time (ie. a few clicks!!)
You can download a trial version of this remarkable easy-to-use data cleansing software suite and try out some merging and matching on your own datasets before you purchase.