Port Selectors
You can create a lookup condition when the Lookup transformation contains generated ports. You can reference a dynamic port or a port selector in a lookup condition. You can also use an expression parameter to parameterize the complete lookup expression.
When the dynamic port contains multiple generated ports, you can define a port selector to filter the generated ports in the lookup condition. The lookup source might change in a dynamic mapping. You can configure a port selector to filter which ports to use for the lookup column. The lookup source port selector must contain the same number of ports as the input columns port selector.
For example, Lookup_PortSelector contains the following ports:
C_CustKey
C_OrderKey
Input_PortSelector contains the following input ports:
CustomerID_IN
OrderID_IN
The following image shows a lookup condition that contains port selectors:
The lookup condition expands to the following expression:
C_CustKey = CustomerID_IN AND C_OrderKey = OrderID_IN
When the lookup condition contains multiple ports, you can configure one operator. For example, you can change the operator to greater than (>). The lookup condition expands to the following expression:
C_CustKey > CustomerID_IN AND C_OrderKey > OrderID_IN
You can create a lookup condition that contains a dynamic port:
Lookup_PortSelector = Dynamic_Input_Port
The dynamic port must contain the same number of ports as the port selector.
Port Selector Configuration
When you configure a port selector, you define selection rules to determine which generated ports to include. The selection rules are similar to the input rules that you can configure for dynamic ports.
A port selector can include static or generated ports. Configure a port selector on the Port Selector tab.
The following image shows the Port Selector tab:
Configure the following properties for a port selector:
- Name
- Identifies the port selector. You can create multiple port selectors in a transformation and reference them in expressions.
- Scope
- Identifies a group of ports that the port selector applies to. You must choose the scope when you create a port selector for a Joiner or a Lookup transformation. These transformations have multiple input groups. The Joiner transformation has a Master or a Detail scope. The Lookup transformation has an Import or a Lookup scope. The Expression transformation has one input group. The scope is always All Ports.
- Selection Rules
- Determines the ports to include in the port selector. When you create the selection rules, the Port Preview panel shows the ports that qualify from the current input ports. These ports might change. Configure the selection rules to accommodate ports from different sources.
Selection Rules
The selection rules associated with a port selector determine the ports to include in the port selector.
When you create the selection rules, the Port Preview panel shows the ports that qualify from the current input ports. These ports might change. Configure the selection rules to accommodate ports from different sources.
Create selection rules based on the following criteria:
- Operator
- Includes or excludes the ports that selection rules return. Default is include. You must include ports before you can exclude ports.
- Selection Criteria
- The type of selection rule you want to create. You can create a rule based on the column name, port type, pattern, or complex data type definition. To include ports based on the column name, search for specific names or search for a pattern of characters in the name.
- Detail
- The values to apply to the selection criteria. If the selection criteria is by column name, configure the string or name to search for. If the selection criteria is by port type, select the port types to include.
The following table describes the selection criteria and how to specify the details for the criteria:
Selection Criteria | Description | Detail |
|---|
All | Includes all ports. | No details required. |
Name | Filters ports based on the port name. | Select the port names from a list of values or use a parameter of type Port or Port List. |
Type | Filters ports based on the data type of each port. | Select data types from a list. |
Pattern | Filters ports by a string of characters in the name or by a regular expression. | Choose prefix, suffix, or regular expression as the pattern type for the port name. Then, enter a value for the pattern or use a parameter of type String. |
Complex Data Type Definition | Filters ports by a complex data type definition. | Choose prefix, suffix, or regular expression as the pattern type for the complex data type definition. Then, enter a value for the pattern or use a parameter of type String. |
Parameterize the Lookup Condition
You can configure an expression parameter that defines the lookup condition. An expression parameter contains a complete expression that you create in an expression editor. You can define a mapping parameter to override the expression parameter at run time.
If you specify a lookup condition using a parameter, you can browse for an expression parameter or you can create a parameter.
The following image shows where to configure an expression parameter for the Lookup condition:
To create a parameter, click New. Define a name for the parameter and edit the default value. The expression parameter default value is the full expression to define the lookup condition. You can use generated ports, dynamic ports, and port selectors in the expression.
Note: When you create the expression, the lookup column is always the first value and the input column is the second value.
For example, you create the following lookup condition in an expression parameter:
CustomerID_IN = C_CUSTKEY
CustomerID_IN is the lookup column.
C_CUSTKEY is the input column.
The following image shows the lookup expression in the expression editor:
Creating a Port Selector
Create a port selector to determine which ports to use in a dynamic expression, a lookup condition, or a joiner condition.
1. Click the Port Selectors tab.
2. In the Port Selectors area, click New.
The Developer tool creates a port selector with a default selection rule that includes all ports.
3. In the Port Selectors area, change the port selector name to a unique name.
4. If you are working on the Joiner transformation or the Lookup transformation, choose the scope.
The available ports change based on the group of ports that you choose.
5. In the Selection Rules area, select an Operator.
- - Include. Create a rule that includes ports for the port selector. You must include ports before you can exclude ports.
- - Exclude. Create a rule that excludes specific ports from the port selector.
6. Choose the Selection Criteria.
- - By Name. Select specific ports by name. You can select the port names from a list of ports in the scope.
- - By Type. Select ports by type. You can select one or more data types.
- - By Pattern. Select ports by a pattern of characters in the port name. You can search with specific characters or you can create a regular expression.
The following image shows the Port Selector tab:
7. Click the Detail column.
The Input Rule Detail dialog box appears.
8. Select the values to filter ports by.
- - By Name. Choose to create a port list by value or by a parameter. Click Choose to select the ports in the list.
- - By Type. Select one or more data types from a list. The Port Preview area shows ports of the types that you select.
- - By Pattern. Choose to search the prefix or suffix of the port name for a specific pattern of characters. Or, choose to create a regular expression to search with. Configure a parameter or configure the pattern to search with.
The Port Preview area shows the ports in the port selector as you configure the rules.
9. To reorder the ports in the port selector, select Reorder generated ports according to the input rule order.
