Tool for merging records
We are importing 60k records that are currently one ITEM record, we are splitting these into CONTACTS. CASES and *ITEMS. Because no contacts existed previously we will get one contact created for...
Forum Discussion
Owen,
You are cleaning up and normalizing your data. There is a lot of ways to do this but I am not aware of a single product that claims to do this. I am sure that the major ETL tools like MuleSoft or Talend can solve the issue, but you will have to know how to develop the logic in those to extract and clean the data. However you are already subscribed to a the most flexible platform in the PaaS market, Quick Base.
Create a new Quick Base application. I always call it Normalization.
Import all 60K records into a blank table called Raw Data and have it auto create the fields.
Now you need to build the reports and additional tables to clean up your Raw Data
Assume that in Raw Data you have the field [Email Address] and this is going to determine if you have a unique Contact
Create a Table Called Contact
Use Excel to DeDup your 60K records on the field Email Address. You will now have less than 60K records
Import those to the Contacts Table
Relate the Contact Table to the Raw Data table but use Email Address as the Reference Field
Add a field to Raw Data [Approved Contact Record]
Now reviewing the Contact Records you can do a Grid Edit and mark which record in Raw Data is going to be approved to go to your other application.
------------------------------
Don Larson
Paasporter
Westlake OH
------------------------------
You are cleaning up and normalizing your data. There is a lot of ways to do this but I am not aware of a single product that claims to do this. I am sure that the major ETL tools like MuleSoft or Talend can solve the issue, but you will have to know how to develop the logic in those to extract and clean the data. However you are already subscribed to a the most flexible platform in the PaaS market, Quick Base.
Create a new Quick Base application. I always call it Normalization.
Import all 60K records into a blank table called Raw Data and have it auto create the fields.
Now you need to build the reports and additional tables to clean up your Raw Data
Assume that in Raw Data you have the field [Email Address] and this is going to determine if you have a unique Contact
Create a Table Called Contact
Use Excel to DeDup your 60K records on the field Email Address. You will now have less than 60K records
Import those to the Contacts Table
Relate the Contact Table to the Raw Data table but use Email Address as the Reference Field
Add a field to Raw Data [Approved Contact Record]
Now reviewing the Contact Records you can do a Grid Edit and mark which record in Raw Data is going to be approved to go to your other application.
------------------------------
Don Larson
Paasporter
Westlake OH
------------------------------
- Thanks Don,
We were considering this way of doing it but I also know that many bespoke databases already come with such features built in. I was really hoping we could do it another way. Partly because if duplicates are created in future we would be able to continue to combine/merge records.
Thanks for these clear instructions which no doubt will be helpful if we are unable to find a tool
Owen
------------------------------
Owen Morgan
------------------------------- Owen,
Quick Base does have some features that can help you but does not have a workflow that automatically does this. On each field you can set whether it is required and unique.
------------------------------
Don Larson
Paasporter
Westlake OH
------------------------------
