Using WHERE to Clean a Single Address
The first way to pass parameters to the AddressDoctor
synonym is with a WHERE condition. While this method only lets you
pass a single parameter at a time, is it the easiest method to understand.
Example: Cleaning an Address With Fielded Data
- Create a data flow. Right-click on an application directory
and select New, Flow.
- Add the AddressDoctor synonym as a source. Expand the application
directory where you created the AddressDoctor synonym, drag the
synonym to the left side of the data flow.
- Right-click on the SQL object and select WHERE filter.
The WHERE Filter Calculator opens.
- Click on the Relational Expressions tab.
- Double click on the fields COUNTRY1, LOCALITY1, PROVINCE1, STREET1,
and STREET_NUMBER1.
- Under Relation, click on the pull-down and select the equal
sign (=) for each row. Under Value, type in the elements of your
address, enclosed in quotes, as shown below.
- Click on OK to close the window.
- Double click on the SQL object to open it.
- On the left side of the Column Selection dialogue, right-click
on Name and select Segment and
Field View.
- Scroll down to the RESPONSE segment and click on PROCESSSTATUS to
select it.
- Scroll down to the STREET_NUMBER segment and select the STREET_NUMBER field
below it. Repeat for STREET, PROVINCE, LOCALITY, POSTALCODE and
COUNTRY.
- Click OK to close the Column Selection
window.
- Right-click on the right side of the Data Flow, select Add Target, New.
- A new target object is added to the Data Flow. Right-click on
the object and select Properties and change the database, synonym
name and other options as desired.
- Click on the run button and wait for the flow to complete.
- Right-click on the target object and select Toggle.
Click on the Sample Data tab to see the results.
Note that the STREET
has been changed to the U.S.P.S standard PENN PLZ and that the POSTALCODE
has the full nine digit Zipcode. Also that PROCESSSTATUS has the value
C4 which means Corrected - all postal relevant elements have been checked.
Example: Cleaning an Address With Partially Fielded Data
- Create a data flow. Right-click on an application directory
and select New, Flow.
- Add the AddressDoctor synonym as a source. Expand the application
directory where you created the AddressDoctor synonym, drag the
synonym to the left side of the data flow.
- Right-click on the SQL object and select WHERE filter.
The WHERE Filter Calculator opens.
- Click on the Relational Expressions tab.
- Double click on the fields ADDRESSLINE1, ADDRESSLINE2 and ADDRESSLINE3.
- Under Relation, click on the pull-down and select the equal
sign (=) for each row. Under Value, type in the lines of your address,
enclosed in quotes as shown below.
- Click on OK to close the window.
- Double click on the SQL object to open it.
- On the left side of the Column Selection dialogue, right-click
on Name and select Segment and
Field View.
- Scroll down to the RESPONSE segment and click on PROCESSSTATUS to
select it.
- Scroll down to the ORGANIZATION segment and select ORGANIZATION below
it. Note that this field is not cleansed by AddressDoctor.
- Repeat for SUBBUILDING, STREET_NUMBER, STREET, PROVINCE, LOCALITY, POSTALCODE
and COUNTRY.
- Click OK to close the Column Selection
window.
- Right-click on the right side of the Data Flow, select Add Target, New.
- A new target object is added to the Data Flow. Right-click on
the object and select Properties and change
the database, synonym name and other options as desired.
- Click on the run button and wait for the flow to complete.
- Right-click on the target object and select Toggle.
Click on the Sample Data tab to see the results.
Note that the floor
was separated out into SUBBUILDING, and that the City, State and
Zip values were separated out into the correct fields.