Merging Records

Forum for DBF Commander discussing.

Merging Records

Postby audidriver » Tue Jan 24, 2012 7:12 am

I have 47,000 records with 35 fields in each record. I have many duplicates where the company name, address, and phone are the same. I'll use XYZ Company as an example.

Record 1 through 10 are the same as far as the first 10 fields go. Then I have fields 11 through 35 that have a Y, 1, 2, 3, 4, 5, or are blank. Record 1 might have a Y in fields 14 and 22 but blank in the rest and Record 2 might have a 3 in 23 and 28 but the rest are blank. I would like to be able to merge the records into one based on the company's name with fields 11 through 35 including any Y,1,2,3,5, or blanks from the 10 duplicate records. I obviously do not want to overwrite a value that is in one record with a blank from the next record. In the end, I want there to be one record of XYG company and showing a Y in fields 14 and 22 and a 3 in fields 23 and 28.

Thoughts?

FYI....I'm using a purchased copy of Professional 2.3
audidriver
 
Posts: 2
Joined: Fri Jan 13, 2012 5:14 am

Re: Merging Records

Postby Support » Tue Jan 24, 2012 9:02 am

Hi!
You can merge your records with the following SQL query:
Code: Select all
SELECT DISTINCT(f1) AS f1,f2,f3,MAX(f4) AS f4,MAX(f5),MAX(f6),MAX(f7),MAX(f8),MAX(f9),MAX(f10) FROM table1 GROUP BY f1,f2,f3


I took a DBF table with 10 fields. You need to modify this 10 field names to your 35 field names in the SQL query.
Here:
f1, f2, f3 - fields with the same values (from 1st to 10th in your case, e.g. company name, and so on)
f4 - f10 - fields with different values, or blank (from 11th to 35th in your case)

Note, values are merged with MAX() function. This means, if you have an empty and non-empty values, the result will be non-empty value. But, on grouping 2 non-empty values the result will be the greater value (2 > 1, 'z' > 'a', and so on). Thus, please be sure you merge blank and non-blank values, otherwise you'll lose one of your non-empty values.
(If you didn't understand this, I can provide an example :) )

The SQL query above creates a temporary DBF file containing rows have been merged. If you need to save the result recordset to DBF file, use the INTO clause:
Code: Select all
SELECT ... FROM ... GROUP BY ... INTO TABLE ...

For more information on SELECT, GROUP BY, and INTO TABLE clauses please refer to SELECT SQL Statement Help

P.S. Every user will get a help, whether purchased license or not ;)
Feel free to ask a question - I'll try to help in anyway!
User avatar
Support
 
Posts: 31
Joined: Mon Feb 07, 2011 10:16 am


Return to DBF Commander Professional

Who is online

Users browsing this forum: No registered users and 11 guests

cron