Supressing Similar Names

[buckslor]

Hi

Is there a way to suppress similar names in Crystal Reports XI? It can be either the first name or last name. The number of letters is usually the same just in the wrong places but not always. There are names with middle initials and there are some without.

My database contains a lot of mis-spelled names and would like to filter these out as much as possible. The names are unique and it doesn't matter which one is displayed as long as there is at least one.

e.g.
John Doe
Jonh Doe
John Dae
Jhon Doe
John J Doe
John d Joe

thank you for your help.

 

[briangriffin]

Depending on your data source, you could use the soundex function to group them. For your sample data soundex evaluated all of them as being phonetically the same, so you could group on that value.

I tried this as a command object in SQL Server, but Oracle works in a similar way:

select soundex('John Doe')
union
select soundex( 'Jonh Doe')
union
select soundex('John Dae')
union
select soundex('Jhon Doe')
union
select soundex('John J Doe')
union
select soundex( 'John d Joe')


These all equate to a soundex value of J500, which is meaningless but would allow you to group them together and suppress the detail section. Then rather than display J500 as a group name, replace it with a min or max of your name field, since you say it doesn't matter which one is shown.

 

[buckslor]

Hi BrianGriffin

I'm using sql as my database. That's a good start. I have thousands of different names so how can I go about this easily all across the board?

Thanks again

 

[lbass]

Do you have some ID field associated with the name? If you grouped on the ID, you could then identify the IDs with multiple names by using group selection:

distinctcount({table.name},{table.ID}) > 1

Then you could either fix the incorrect names or move the names into the ID group section to show one name per ID.

-LB

 

[buckslor]

Hi lbass

The only ID field that I can think of is the customer number - everything else is different.

There are many customers with the same names - just not always spelled the same. I have something similar that already groups by Name and suppresses the same exact spellings already just that there are a few mis-spelled names listed as well as the correct one.

The distinctcount doesn't count same names if spelled differently, does it? Could you elaborate a little more, please.

Thank you very much

 

[lbass]

The customer number sounds appropriate, if there should be just one name per customer. Using a distinctcount of the name at the customer group level in the group selection formula would show you which accounts have mispelled names. The problem is knowing which of the multiple names is the correct one--so how do YOU know which is the one that is correct?

-LB