Input pipe range lookup

You can set up input pipes to look up a range of values rather than matching values. When you use a range lookup, the field represents the lower limit of the range. You can use range lookups for both standard and matrix lookup input pipes.

For example, assume you have Cost values that are specified for ranges of distance values:

In this case, 25 miles will use the 1 mile value, since this value represents a range of >=1 and < 50. In this example, if you are looking up a 0.5 mile value, the input pipe lookup will fail.

Create a range lookup for an input pipe

1. For the input pipe, select either matrix Lookup or standard Lookup.
2. Select the Data Source, then select the Target Table and Field to use.
3. For a matrix lookup:
   • Select the Row Lookup Value, and the Column Lookup Value.
   • Select the Use Row As Range option if the row is acting as a range.
   • Select the Use Column As Range option if the column is acting as a range.

     See Creating input pipes with two range lookups for information about setting up a data source for two range lookups.

4. For a standard lookup:
   • Choose the Data Source Field.
   • When configuring the Map From Data Source Field and Map To Table Field, select the field that contains the range setting, then select the Is Range option.
   • Only one field can be used as a range lookup field per input pipe.

Creating input pipes with two range lookups

When creating a matrix lookup input pipe, you can define a range lookup on two fields. This allows you to set ranges for both Shipment Size and Distance values in one input pipe, for example. You can create matrix lookup input pipes using Excel or Access data sources. When you use a range lookup, the field represents the lower limit of the range.

Assume that for your Transportation Policies, you have Variable Transportation Cost values that are specified for ranges of Shipment Sizes within Distance value ranges. For example:

if the Distance is >= 0 and < 100 miles, the Variable Transportation Cost is:

• 2 for Shipment Sizes >= 0 and < 50
• 1.8 for Shipment Sizes between >= 50 and <100
• 1.5 for Shipment Sizes greater than 100

if the Distance is >= 100 and < 500 miles, the Variable Transportation Cost is

• 2.25 for Shipment Sizes >= 0 and < 50
• 2 for Shipment Sizes between >= 50 and <100
• 1.75 for Shipment Sizes greater than 100

and so on. To use the two ranges, you could create an Excel spreadsheet as shown below. Column A is the set of Distance range values (0, 100, 500). The numeric values in B1, C1 and D1 are the Shipment Size range values (0, 50, 100):

When you create the matrix lookup, define Distance as the Row Lookup Value and Shipment Size as the Column Lookup Value. Also select the Use Row As Range and Use Column As Range options.