Transform Guide

This guide describes the capabilities of the Transform function within DQ for Dynamics™. 

Transform functions enable the User to Abbreviate, Elaborate, Exclude and Normalize data in order to broaden the net and identify duplicates with more accuracy.

DQ for Dynamics™ utilises a variety of transformation libraries, some of which include: Business, Countries, First Names and Addressing.

For example, by using Abbreviate with Country, we can transform United Kingdom to UK.

The Transform function is compatible with 5 different spoken languages.

Elaborate - allows you to transform (lengthen) the structure of your data to ensure a consistent format.

For example you can choose to elaborate business elements in a company name field. This will expand 'Ltd' to 'Limited', 'Grp' to 'Group', 'Inc.' to 'Incorporated' etc. and write the results directly back to the attribute selected in your data set.

As with all rules there are times when you would not wish to use this rule. For example in a Forename field it would be very dangerous to elaborate your data. Pete would go to Peter, Bob to Robert etc. but you cannot define what Sam would go to e.g. Samuel or Samantha, so Sam would be left the same. However if there were a Rob in your database that is short for Robin this would also be transformed to Robert.

Abbreviate - allows you to transform (shorten) the structure of your data to ensure a consistent format.

For example you can choose to Abbreviate business elements in a company name field. This will reduce 'Limited' to 'Ltd', 'Group' to 'Grp', 'Incorporated' to 'Inc' etc. and write the results directly back to the field you select in your data set.

Normalise - allows you to transform the structure of your data to ensure a consistent format when Perfect & Merge looks at your data (Data Matching Mode).

For example: You can choose to Normalise business elements in a company name field. This will reduce 'Limited' to 'Ltd', 'Group' to 'Grp', 'Incorporated' to 'Inc.' etc. and write the results directly back to the field you select in your data set.

As with all rules there are times when you would not wish to use this rule. For example in a Forename field it would be very dangerous to Normalise your data. Peter would go to Pete, Robert to Bob, even Rob to Bob etc. you cannot define which of the following names, Samuel or Samantha, as both would be normalised to Sam. However if there were a Rob in your database that is short for Robin, this would be normalised to Bob.

Exclude - allows you to transform the structure of your data, by removing certain  to ensure a consistent format when Perfect & Merge looks at your data (Data Matching Mode).

For example, you can choose to exclude business elements in a company name field. This will remove 'Ltd', 'Limited', 'Grp', 'Group', 'Inc', 'Incorporated' etc. 

Best used during the matching process as it enables Perfect & Merge to look at the core element of the Company Name to provide a match. E.g. Perfect & MergeLtd. will match with Perfect & MergePlc.

Addressing elements are used on address fields, usually Address Line 1 and Address Line 2.

This category understands the most common elements of address data. For example 'Gnds' is an abbreviation for 'Gardens', 'Rd' is 'Road', 'Av' is 'Avenue' etc. 

Using this function 15 Hound Terrace will match 15 Hound Street. This would not match if you use the Normalise transformation, however, 15 Hound St. will match 15 Hound Street.

Again this is potentially dangerous if selected as a match field on its own, but when this address field is accompanied by a match criteria which is defined on Postcode as well, it becomes a lot more accurate. 

See the example below to better understand how this record is definitely a match, however, some addressing elements have been incorrectly captured during data input.

Business Name elements are used on company name fields.

This category understands the most common elements of business name data. For example 'Ltd' is an abbreviation for 'Limited', 'Plc' is 'Public Limited Company', 'Grp' is 'Group' etc. 

Best used during the Data Matching process using the Exclude Transformation to ensure that the matching process focuses on the core part of the company name. E.g. using this function 'Fictitious Ltd' will match 'Fictitious Plc' This would not match if you use Normalise however 'Fictitious Ltd' will match 'Fictitious Limited'

Again, this is potentially dangerous if selected as a match field on its own, but when this address field is accompanied by a match defined on address elements as well, it becomes a lot more accurate. 

See the example below to better understand how this record is definitely a match. You can see the business names have matched on the main 'Fictitious', but have also matched on the 'addressing' elements, ie., Postcode. 

The Countries category is used on an address field that contains the information about which country that record relates to.

This category understands most common styles of Country data. For example 'UK' is an abbreviation for 'United Kingdom', 'USA' is 'United States of America', 'De' is 'Germany' etc.

Best used during the Data Matching process using the Normalise Transformation to ensure that the matching process standardises the format of the Country in the data set.

E.g. using this function 'United Kingdom' will match 'UK'.

The Dates/Events Category would only be used when your data set contains date information that you wish to match on.

This category understands most common styles that a date can be written in. For example 'Jan' is an abbreviation for 'January', 'Feb' is 'February', 'Mon' is 'Monday' etc.

Best used during the Data Matching process using the Normalise Transformation to ensure that the matching process standardises the format of the date field.

Job Title elements are used on Name fields and Job Title fields.

This category understands most common elements of Job Title data. For example 'Mgr' is an abbreviation for 'Manager', 'Mkt' is 'Marketing', 'Col' is 'Colonel' etc. 

Best used during the Data Matching process using the Exclude Transformation to ensure that the matching process focuses on the core part of the persons name if the title is in the same field. However if the Job Title is in a separate field it is good practice to Normalise this data during the matching process, if it was relevant to your session.

E.g. using this function 'Marketing Manager' will match 'Mkt Mgr'.

The Numbering Category should be used when you have different number formats in your database.

This category understands most common styles that a number can be written in. For example '10' could also be 'Ten', '1000' could also be 'One Thousand', '1st' could also be 'First', '2nd' could be 'Second' etc.

Best used during the Data Matching process using the Normalise Transformation to ensure that the matching process standardises the format of the field that contains numbers.

E.g. using this function ‘1 to 1’ will match ‘One to One’ in a company name field.

Qualification elements are usually used on Name fields.

This category understands most common elements of qualifications that can be added to a person’s name. For example qualifications include 'Bsc' as an abbreviation for 'Bachelor of Science', 'Phd' is 'Doctor of Philosophy', 'MSc' is 'Master of Science' etc. 

Best used during the Data Matching process using the Exclude Transformation to ensure that the matching process focuses on the core part of the person’s name.

E.g. using this function 'Mr Robert Dickson BSc' will match 'Bob Dixon' during a phonetic match.

Salutation elements are commonly used on name fields.

This category understands most common elements of name data. For example 'Mr' 'Mrs' 'Ms'. 

Best used during the Data Matching process using the Exclude Transformation to ensure that the matching process focuses on the core part of the person’s name. 

E.g. using this function 'Mr Robert Dickson' will match 'Bob Dixon' during a Phonetic match as the 'Mr' is excluded.

The Weights/Measures Category would only be used when your data set contains this type of information and you wish to match on this field.

This category understands most common styles of that a weight or measure can be written in. For example 'Oz' is an abbreviation for 'Ounce', 'Kg' is 'Kilogram' etc.

Best used during the Data Matching process using the Normalise Transformation to ensure that the matching process standardises the format of the date field.

E.g. using this function ’12 Oz’ will match ’12 Ounces’

The Miscellaneous Category is very rarely used.

This category understands some obscure transformations that may be required in the matching process. For example ‘pm’ is an abbreviation for 'Post Meridian', ‘am’ is 'Ante Meridian', ‘&’ is 'and' etc.

Best used during the Data Matching process using the Normalise Transformation to ensure that the matching process standardises the format of the date field.

E.g. using this function ’Tate & Lyle’ will match ’Tate and Lyle’

First name elements are used to standardise name fields.

This category understands most common elements of forename data. For example ‘Bill’ can be an abbreviation for 'William', ‘Bob’ can be an abbreviation for 'Robert' etc. 

Best used during the Data Matching process using the Normalise Transformation to ensure that the matching process standardises the forename.

E.g. using this function 'Robert Dickson' will match 'Bob Dixon'. 

Selecting to exclude Forenames would mean that Perfect & Merge would ignore a forename in a name field during the matching process. This can be advantageous when you have Robert Dickson in a single field and you only wish to match on surname. E.g. exclude initials and exclude forename and R Dixon will match Bob Dixon.

This has a potential danger if the both elements of the name are in the same field and the name is made up of two words that can be interpreted as forenames, such as George Michael or Elton John would have both elements excluded. They therefore are seen as a blank record and a blank will match another blank.