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.


Top of page

Example: Cleaning an Address With Fielded Data
  1. Create a data flow. Right-click on an application directory and select New, Flow.
  2. 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.
  3. Right-click on the SQL object and select WHERE filter. The WHERE Filter Calculator opens.
  4. Click on the Relational Expressions tab.
  5. Double click on the fields COUNTRY1, LOCALITY1, PROVINCE1, STREET1, and STREET_NUMBER1.
  6. 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.

  7. Click on OK to close the window.
  8. Double click on the SQL object to open it.
  9. On the left side of the Column Selection dialogue, right-click on Name and select Segment and Field View.
  10. Scroll down to the RESPONSE segment and click on PROCESSSTATUS to select it.
  11. Scroll down to the STREET_NUMBER segment and select the STREET_NUMBER field below it. Repeat for STREET, PROVINCE, LOCALITY, POSTALCODE and COUNTRY.

  12. Click OK to close the Column Selection window.
  13. Right-click on the right side of the Data Flow, select Add Target, New.
  14. 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.
  15. Click on the run button and wait for the flow to complete.
  16. 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.


Top of page

Example: Cleaning an Address With Partially Fielded Data
  1. Create a data flow. Right-click on an application directory and select New, Flow.
  2. 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.
  3. Right-click on the SQL object and select WHERE filter. The WHERE Filter Calculator opens.
  4. Click on the Relational Expressions tab.
  5. Double click on the fields ADDRESSLINE1, ADDRESSLINE2 and ADDRESSLINE3.
  6. 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.

  7. Click on OK to close the window.
  8. Double click on the SQL object to open it.
  9. On the left side of the Column Selection dialogue, right-click on Name and select Segment and Field View.
  10. Scroll down to the RESPONSE segment and click on PROCESSSTATUS to select it.
  11. Scroll down to the ORGANIZATION segment and select ORGANIZATION below it. Note that this field is not cleansed by AddressDoctor.
  12. Repeat for SUBBUILDING, STREET_NUMBER, STREET, PROVINCE, LOCALITY, POSTALCODE and COUNTRY.
  13. Click OK to close the Column Selection window.
  14. Right-click on the right side of the Data Flow, select Add Target, New.
  15. 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.
  16. Click on the run button and wait for the flow to complete.
  17. 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.


iWay Software