Resolve missing zip code information

Try Pentaho Data Integration and Analytics
Version
9.3.x
Audience
anonymous
Part Number
MK-95PDIA000-11
Follow these steps to resolve the mising postal code information.
  1. Add a Stream Lookup step to your transformation by clicking the Design tab, expanding the Lookup folder, then selecting Stream Lookup.
  2. Draw a hop from the Filter Missing Zips to the Stream lookup step. In the dialog box that appears, select Result is FALSE.
  3. Create a hop from the Read Postal Codes step to the Stream lookup step.

    Add a hop from Read Postal Codes to Stream Lookup
  4. Double-click the Stream lookup step to open the Stream Value Lookup window.
  5. Rename Stream Lookup to Lookup Missing Zips.
  6. From the Lookup step drop-down box, select Read Postal Codes as the lookup step. Perform the following:
    1. In the key(s) to look up the value(s) table, define the CITY and STATE fields .
    2. In row #1, open the drop-down menu in the Field column and select CITY.
    3. Click in the LookupField column and select CITY.
    4. In row #2, open the drop-down menu in the Field column and select STATE.
    5. Click in the LookupField column and select STATE.

      Stream value lookup example
  7. Click Get Lookup Fields to pull the three fields from the Read Postal Code step.
  8. 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.
  9. In the New Name field, change the name POSTALCODE toZIP_RESOLVED and verify that Type is set to String.
  10. Select Use sorted list (i.s.o. hashtable).

    Value lookup example
  11. Click OK to close the Stream Value Lookup edit properties dialog box.​​
  12. Save your transformation.