Delete after Nth number of Duplicates

Build your list of AccuZIP6 commands by requesting commands and sharing commands with other users

Moderators: Chrissy, Steve, Terry, Donna, Dave, Eric, MartyH, Kristen, Geoff, Vince

Post Reply
User avatar
Eric
Moderator
Posts: 1910
Joined: Tue Sep 19, 2006 3:29 pm
Contact:

Delete after Nth number of Duplicates

Post by Eric » Wed Jan 09, 2008 7:36 am

This command will search for instances where there are more than 3 of the same record in a database. When there are more than three, it will delete the other duplicates, so there are no more than a maximum of three duplicates per record. If you would like to change that value, you can modify the line in the command that reads:
delete for val(groupct)=>3

BACKUP YOUR DATA FIRST

Paste the Command below into the Command Statement Syntax area, and give the command a name in the Save As box of the Command window, such as Delete After Nth Number of Duplicates.
Open the database you would like to perform the search on.
Run the duplicate search as you normally would
IMPORTANT: Make sure you select the option in the duplicate search window to "Backup Duplicates"!
Update the database with the results of the Duplicate search.
Pull down the Edit menu to Command, then run the command below.


Code: Select all

ON ERROR com_message=message()
com_alias=alias()
PUBLIC cDupFilePath,comAlias,comUnique
PUBLIC ARRAY comArFiles(1,1)
SET SAFETY OFF
ALTER TABLE (comAlias) ADD COLUMN GROUPCT C(4)
ALTER TABLE (comAlias) ADD COLUMN priority C(1)
replace all priority with ""
replace all groupct with ""
* Get the current Databases Alias Name
comAlias=ALIAS()
* Capture all back file names in memory
=ADIR(comArFiles,ADDBS(JUSTPATH(DBF(comAlias)))+"*.rpt")
* Create the variable name for the duplicate records
cDupFilePath=ADDBS(JUSTPATH(DBF(comAlias)))+FORCEEXT(comArFiles(1,1),'dbf')
comUnique=SYS(2015)
USE (cDupFilePath) IN 0 ALIAS (comUnique)
SELECT(comUnique)
INDEX ON therec_xx tag therec_xx
SELECT(comAlias)
REPLACE priority WITH IIF(!DELETED(),'1','2') GROUPCT with ALLT(TRANSFORM(&comUnique..thedupcnt_)) for SEEK(RECNO(),comUnique)
RECALL ALL
USE IN IIF(USED(comUnique),SELECT(comUnique),0)
RENAME (FORCEEXT(cDupFilePath,"rpt")) ;
TO (cDupFilePath+".txt")
delete for val(groupct)=>3
alter table alias() drop column priority
alter table alias() drop column groupct
PACK
Chat with an AccuZIP6 technician weekdays from 7-4 PST with AccuZIP6 Live Online Support

Post Reply