Explains how to resolve missing zip code information.
After you Retrieve Data from your Lookup File, you can begin to resolve the missing zip codes.
- Add a Stream Lookup step to your transformation. To do this, click the Design tab, then expand the Lookup folder and choose Stream Lookup.
- Draw a hop from the Filter Missing Zips to the Stream lookup step. In the dialog box that appears, select Result is FALSE.
- Create a hop from the Read Postal Codes step to the Stream lookup step.
- Double-click on the Stream lookup step to open the Stream Value Lookup window.
- Rename Stream Lookup to Lookup Missing Zips.
- From the Lookup step drop-down box, select Read Postal Codes as the lookup step.
- Define the CITY and STATE fields in the key(s) to look up the value(s) table. Click the drop down in the Field column and select CITY. Then, click in the LookupField column and select CITY. Perform the same actions to define the second key based on the STATE fields coming in on the source and lookup streams:
- Click Get Lookup Fields.
- POSTALCODE is the only field you want to retrieve. To delete the CITY and STATE lines, right-click in the line and select Delete Selected Lines.
- Give POSTALCODE a new name of ZIP_RESOLVED and make sure the Type is set to String.
- Enable Use sorted list (i.s.o. hashtable).
- Click OK to close the Stream Value Lookup edit properties dialog box.
- Save your transformation.
- To preview the data:
- In the canvas, select the Lookup Missing Zips step then right-click and select Preview.
- In the Transformation debug dialog window, click Quick Launch to preview the data flowing through this step.
- The Examine preview data window appears. Notice that the new field, ZIP_RESOLVED, has been added to the stream containing your resolved postal codes.
- Click Close to close the window.
- If the Select the preview step window appears, click the Close button.
- Note that the execution results near the bottom of the Spoon window shows updated metrics in the Step Metrics tab.