Standardise Abbreviations
I will be getting a list of company names.
I want to abbreviate certain string in the name to choice 1.
I may find either the full string which I want to shorten to choice1. Alternatively they may have used choices 2, 3 or 4 which must be changed to choice1.
For example Rentenfonds and Rtfd should be changed to Rentfds.
Where a choice1 abbreviation has different meanings in the same market it is ambiguous and should be ignored. For example CP has 2 meanings in Brazil and should be ignored.
Where a choice1 abbreviation has different meanings in one market and a different meaning in "ALL" then expand it according to it's meaning for "ALL" for every market besides the one where it has a different meaing.
For example TR should be expanded to Trust for all marekts besides UK. In the UK it should be expanded to Total Return.
Full Name Choice 1 Choice 2 Choice 3 Choice 4 Market
Companies Cos ALL
Credit Suisse CP Curto P Brazil
Curto Prazo CP Brazil
Corto Plazo CP Spain
Conta de referencia CR Brazil
Credito Privado CrdPri C P Brazil
Credit Crdt ALL
Crecimiento Crec Spain
Rentenfonds Rentfds Rentfds Rentfd Rtfd Germany
Republic Rep Repub ALL
Reserve/Reserves Res Rsv(s) Rsrv(s) ALL
TR Trust ALL
TR Total Return UK
I want to abbreviate certain string in the name to choice 1.
I may find either the full string which I want to shorten to choice1. Alternatively they may have used choices 2, 3 or 4 which must be changed to choice1.
For example Rentenfonds and Rtfd should be changed to Rentfds.
Where a choice1 abbreviation has different meanings in the same market it is ambiguous and should be ignored. For example CP has 2 meanings in Brazil and should be ignored.
Where a choice1 abbreviation has different meanings in one market and a different meaning in "ALL" then expand it according to it's meaning for "ALL" for every market besides the one where it has a different meaing.
For example TR should be expanded to Trust for all marekts besides UK. In the UK it should be expanded to Total Return.
Full Name Choice 1 Choice 2 Choice 3 Choice 4 Market
Companies Cos ALL
Credit Suisse CP Curto P Brazil
Curto Prazo CP Brazil
Corto Plazo CP Spain
Conta de referencia CR Brazil
Credito Privado CrdPri C P Brazil
Credit Crdt ALL
Crecimiento Crec Spain
Rentenfonds Rentfds Rentfds Rentfd Rtfd Germany
Republic Rep Repub ALL
Reserve/Reserves Res Rsv(s) Rsrv(s) ALL
TR Trust ALL
TR Total Return UK
ASKER
I know about relationships (primary and foreign key constraints). Not so hot on normalisation. I think it's to do with each data item appearing once. We use it, but it often seems to cause as many problems as it solves.
The problem is the suppliers of the data are using this system of abbreviations. Perhaps I should just use dynamic SQL or some other method to generate replace statements eg
@parameter =replace(@parameter,Fullna
and exclude ambiguous choices (not sure exactly how).
The problem is the suppliers of the data are using this system of abbreviations. Perhaps I should just use dynamic SQL or some other method to generate replace statements eg
@parameter =replace(@parameter,Fullna
and exclude ambiguous choices (not sure exactly how).
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
thanks.
In a true "Normalized" system each "Value" should only have one meaning, else you should be using a different abbreiviation.
Also you should be referencing a Primary Key Numeric field, (ex.: Autonumber), not the text value.
This means that you "could" have two two abbreviations that were the same "textually", ...but since you would be referencing the numeric (unique) value, it would not matter.
To do what you are asking for here would require far to much work (in implementation and maintenance) to allow for each exception contingency.
In other words, just adding records to a table should not have to involve complex logic.
How familiar are you with the rules of database design (Normalization, Relationships, key fields, ...etc)?
JeffCoachman