Identifying duplicate records

[lsmyth]

I want to identify possible duplicate records in a system and produce a report which lists the possible duplicates as separate rows for comparison so that they can be manually merged on the system.

Is there any way of doing this using Business Objects?

Please help...

L

 

[BrianTyler]

You need to end up with something that looks a bit like:

select col1,col2... from main_table
where main_table.key in
(select main_table.key from main_table
group by main_table.key
having count(*) > 1)

This means a sub-query with a 'calculation'

Good luck

Brian

 

[blom0344]

If there is a main table key (primary) duplicates would not exist
Another possible solution would be to create a universe object like :

[ Oracle example]

count(key) over (partition by key order by key)

If there is not 1 field that defines uniqueness, but 3 then:

count(key1||key2||key3) over (partition by (key1||key2||key3) order by (key1||key2||key3))

Now in your query set a condition that only fetches data where the count object >1

You need to be on DB2 7/8 or on Oracle 8/9/10 for this to work


Ties Blom

 

[lsmyth]

Hi Brian,

The problem that I have is that I want to identify records which have the same Surname, Date of Birth and Postcode, for example, but that they may have a different Unique Identifier.

e.g. I would want to identify the following two records as possible duplicates...

000025 Alex Cairns 12/08/1963 1 Main Street BT4 5CD
001234 Alexander Cairns 12/08/1963 1 Main St BT4 5CD

I hope this makes sense..

L

 

[blom0344]

Your example does not describe duplicates in a database sense. If fields do not match EXACTLY you will be able to solve this with some hefty processing.
Definitely not something BO will be capable of...

Ties Blom

 

[lsmyth]

Maybe I'm not explaining it very well, sorry I'm not very technically minded!

The fields that i want to match on will be identical, such as Surname, DOB and Postcode.

I know it can be done as I have seen similar reports before, I'm just not sure how

 

[blom0344]

Brian's suggestion will work if you can identify a single field that will guarantee uniqueness.
If you have 3 fields that constitute uniqueness than my solution will probably work as well.

I can imagine that solutions offered are a bit technical.
My advise would be to suggest them to either a more experienced report/ query builder and/or your universe designer if the solution I propose is viable.

It really is not that hard. You are almost there..


Ties Blom