The Flat File Lookup Function

If your reference data is kept in a flat text file, there's an easy way to integrate it into a Pervasive Data Integrator.  Use the RIFL function "Lookup" to swap one value out for another while a transformation is running.

Although Lookup is available via the Lookup Wizard in Map Designer, its use is limited because it isn't able to take macros for the input file.  The deployment will be cleaner without the wizard because there will be one fewer RIFL file to deploy.

To use the lookup function, build an expression in one of the target fields.

Lookup(Fields("State"), MacroExpand("$(DATA_DIR)regions.txt"), ",")


This function reads a file under $(DATA_DIR)regions.txt which is delimited by a comma (','). The call uses the source field "State" as a key. Other parameters let you adjust the order by which column the key is identified (first column or second column).

This is the regions.txt file I used as a source for the Lookup function.

State,Region
MD,Northeast
NY,Northeast
CT,Northeast
DE,Northeast
DC,Northeast
CA,West
CA,West
AZ,West
WA,West
WA,West
NC,South
SC,South
VA,South
FL,South
GA,South

The result is a MySQL table that loaded input data -- in this case sales associate contact info -- with the appropriate region given a U.S. state.

The Flat File Wizard is much less useful than the Incore Table Lookup Wizard.  The code listing below is the generated product with comments stripped. Note the absolute path.  I entered a macro in the wizard which allowed me to navigate to a directory, but was expanded when the code was generated.


''''''''''''''Generated by Flat File Lookup Wizard'''''''''''''''''''''
Function SalesRegion_Lookup(KeyValue, DefaultValue)
If DefaultValue <> "" then
SalesRegion_Lookup = Lookup(KeyValue, _
"C:\Users\Public\Documents\Data\regions.txt", ",", 0, DefaultValue)
Else
SalesRegion_Lookup= Lookup(KeyValue, _
"C:\Users\Public\Documents\Data\regions.txt", ",", 0, "")
End if
End Function