Lookups > Enabling a Lookup Wizard

Enabling a Lookup Wizard
Lookup Wizards automate the process of creating lookups for your transformations. You simply name the lookup or select an existing lookup to be edited, browse to files or tables to automatically build connection strings and select the key and returned fields. At the end of each Lookup Wizard, a reusable code module is created in your workspace containing the functions you need for doing lookups. The Code Module files generated by these wizards can be reused in any map you create.
1
From the Tools menu, select Define Lookup Functions.
Open the RIFL Script Editor, and go to the Wizards menu and select Lookup Function Wizards.
2
3
See Also
“Adding and Removing Lookup Code Modules”
“Using Lookup Functions in Expressions”
Enabling the Dynamic SQL Lookup Wizard
1
From the Tools menu, select Define Lookup Functions.
Open the RIFL Script Editor, and go to the Wizards menu and select Lookup Function Wizards.
2
Select the Dynamic SQL Lookup Wizard and click Next.
3
To create a new SQL lookup, click the Create a new Dynamic SQL definition radio button and enter the name you want to give it in the Enter name of new Dynamic SQL definition box. It is suggested you combine the database name and the table name to give a name that is instantly recognizable and meaningful. You cannot use the underscore character in a definition name. This name is used as the lookup code module file name followed by a ".dynsql.rifl" extension.
To use an existing file, check the Change existing Dynamic SQL definition radio button and navigate to the appropriate .dynsql.rifl file in your workspace.
4
Click Next.
5
To create a new DJImport object, click the Enter name of new DJImport Object radio button. Enter the name in the box.
To use an existing DJImport object, check the Select existing DJImport Object radio button and select the object from the list.
6
Click Next.
7
Click the BUILD a connect string radio button and click Build to open the Enter Connection Information window. Select the source connection file, the database/file and table you want to use.
Click the Use an existing connect string radio button and navigate to the .sc.xml source connection file that you want to use.
If you are using an existing table definition, click the Use the connect string from existing table definition radio button.
8
9
Click Next. The following screen displays, showing the fields available from the table you have selected:
image\dynamic_SQL_lookup_wizard_4.png
10
Select the key field in the Key column. This is the value to pass to the lookup.
11
Select the fields you want returned from the lookup in the Include column. You can return as many fields as required.
A lookup returns only one field value. If you select multiple fields, a separate lookup function is created for each field. This way, one code module can contain all the possible lookup functions you might ever want to use for a given connection.
Note A default value must also be specified when you “Using Lookup Functions in Expressions”. This overrides the value set here unless you specify an empty string ("") in the lookup function.
12
Click Next.
image\dynamic_SQL_lookup_wizard_5.png
13
The following dynamic SQL functions can now be accessed from the RIFL Script Editor's Expression Tree under the User Defined Functions node:
SQLtablename_Init( ) – Connects to the SQL table. Use this function from a global spot, such as a BeforeTransformation Event. Once the _Init function is called, all Map events can use the _Lookup functions.
SQLtablename_ fieldname_Lookup (KeyValue, Default Value) – Performs key value lookups. This function can occur once per selected result field in the table.
SQLtablename_Terminate() – Terminates the connection to the SQL table.
See Also
To delete a Lookup Code Module created this way, see “Adding and Removing Lookup Code Modules”.
Enabling the Incore Lookup Wizard
1
From the Tools menu, select Define Lookup Functions.
Open the RIFL Script Editor, and go to the Wizards menu and select Lookup Function Wizards.
2
Select the Incore Lookup Wizard and click Next.
3
To create a new Incore Table lookup, click the Create a new Incore Table definition radio button and enter the name you want to give it in the Enter name of new Incore Table box. It is suggested you combine the database name and the table name to give a name that is instantly recognizable and meaningful. Note that you cannot use the underscore character in a definition name.
To use an existing file, check the Change existing Incore Table definition radio button and navigate to the appropriate .itable.rifl file in your workspace.
4
Click Next.
5
Click the BUILD a connect string radio button and click Build to open the Enter Connection Information window. Select the source connection file, the database/file and table you want to use.
Click the Use an existing connect string radio button and navigate to the .sc.xml source connection file that you want to use.
If you are using an existing table definition, click the Use the connect string from existing table definition radio button.
6
Click Next. The following screen shows the fields available from the table you have selected:
image\incore_lookup_wizard_4.png
7
Select the key field in the Key column. This is the value to pass to the lookup. You can use any of the fields as the key field. Click the appropriate check box.
8
Select the fields you want to be returned from the lookup in the Include column. You can return as many fields as required. Click in the appropriate check boxes.
9
For each selected field, you can specify a Default Value to be returned if a key value is not found.
Note A default value must be specified when you “Using Lookup Functions in Expressions”. This overrides the value set here unless you specify an empty string ("") in the lookup function.
10
Click Next.
image\incore_lookup_wizard_5.png
This screen confirms that the Incore table lookup functions have been created for this table and displays the functions that are now available to you.
11
Click OK.
The following incore functions can now be accessed from the RIFL Script Editor's Expression Tree under the User Defined Functions node.
incoretablename_Clear( ) – Clears an Incore table from resident memory.
incoretablename_Init( ) – Builds an Incore table. You can use this function from a global spot, such as a BeforeTransformation Event. Once the _Init() function is called, all Map events can use the _Lookup functions.
incoretablename_AddRow(KeyValue, Field1Value, FieldnValue) – Adds a new row to an existing Incore Lookup table. Pass parameter values for the key field and one for each result field in the table.
incoretablename_ChangeRow(KeyValue, Field1Value, FieldnValue) – Replaces an existing row in an existing Incore Lookup table. Pass parameter values for the key field and one for each result field in the table.
incoretablename_resultfieldname_Lookup(KeyValue, DefaultValue) – Performs key value lookups. This function can occur once per selected result field in the table.
incoretablename_RemoveRow(KeyValue) – Removes a row from an already existing Incore Lookup table. Passes the key field parameter value.
incoretablename_WriteToFile(FileName, FieldSeparator) – Writes the Incore Lookup table to a file. Pass the filename (including drive and path) and the separation character to be used.
See Also
To delete a Lookup Code Module created this way, see “Adding and Removing Lookup Code Modules”.
Enabling the Flat File Wizard
1
From the Tools menu, select Define Lookup Functions.
Open the RIFL Script Editor, and go to the Wizards menu and select Lookup Function Wizards.
2
Select the Flat File Lookup Wizard and click Next.
3
To create a new flat File lookup, click the Create a new Flat File definition radio button and enter the name you want to give it in the Enter name of new Flat File definition box. It is suggested you use a name that is instantly recognizable and meaningful. Note that you cannot use the underscore character in a definition name.
To use an existing file, check the Change existing Flat File definition radio button and navigate to the appropriate .dynsql.rifl file in your workspace.
4
Click Next.
5
6
The Field Separator/Delimiter used in the table displays. If it is not correct, use the Field Separator/Delimiter list to select the correct one. In the toolbar, click Data Browser to display your file to ensure that the right separator has been selected.
image\flatfile_lookup_wizard_3.png
7
Select the key field in the Key column. This is the value to pass to the lookup.
8
Select the fields you want to be returned from the lookup in the Include column. You can return as many fields as required.
9
For each selected field, you can specify a Default Value to be returned if a key value is not found.
Note A default value must also be specified when you “Using Lookup Functions in Expressions”. This will override the value set here unless you specify an empty string ("") in the lookup function.
10
Click Next.
image\flatfile_lookup_wizard_4.png
This screen confirms that the flat file lookup functions have been created and displays the functions that are now available to you.
11
The following flat file function can now be accessed from the RIFL Script Editor's Expression Tree under the User Defined Functions node.
flatfilename_resultfieldname _Lookup(KeyValue, DefaultValue) – Performs key value lookups.
See Also
To delete a Lookup Code Module created this way, see “Adding and Removing Lookup Code Modules”.