Show Menu
TOPICS×

LookupRows

The LookupRows transformation looks at other log entries with the same tracking ID and sets the value of the output field to the value of a designated field in the input row.
Because the LookupRows transformation performs its lookup on log entries and not lookup files, it is very similar to the CrossRows transformation. See CrossRows .
To work, the LookupRows transformation requires that the data is ordered in time and grouped by the tracking ID in your source data. Therefore, LookupRows works only when defined in the Transformation.cfg file or in a Transformation Dataset Include file.
As you review the descriptions of the parameters in the following table, remember the following:
  • The output row is the row of data that the transformation is working on at a given point in time.
  • Input rows are all of the other rows of data (before, after, or including the output row) whose values of the input field serve as inputs to the transformation.
Parameter Description Default
Name Descriptive name of the transformation. You can enter any name here.
Comments Optional. Notes about the transformation.
Condition Limits the output of the transformation to certain log entries. If the condition is not met for a particular log entry, the field in Output Row Value Output parameter is left unchanged. The input still may be used to affect other log entries.
Input Condition Accepts input for the transformation from only certain input rows. If the Input Condition is not met for a particular input row, the input field from that row is ignored and does not affect other output rows. However, the output field from that row is still modified per the specified Condition.
Input Row Key Input The name of the field to use as the key for the input rows.
Input Row Value Input The name of the field in the input row whose value is copied to the field in the Output Row Value Output parameter if all conditions are satisfied.
Operation
An operation that, for each output row, is applied to all of the input rows satisfying all of the conditions defined by the Input Condition and Input Row Key Input parameters to produce an output:
  • FIRST outputs the value of the field in the Input Row Value Input parameter from the first matching input row in the data (not the first matching row after the output row).
  • LAST outputs the value of the field in the Input Row Value Input parameter from the last input row in the data (not the last matching row before the output row).
Output Row Key Input The name of the field to use as the key for the output row.
Output Row Value Output The name of the field in the output row whose value is copied from the field in the Input Row Value Input parameter if all conditions are satisfied. All output rows with the same x-trackingid and Output Row Key Input values have the same Output Row Value Output value.
The Input Row Key Input, Input Row Value Input, and Input Condition parameters together define the lookup file for each tracking ID, while the Output Row Key Input, Output Row Value Input, and Condition parameters control what is looked up in the file and what value is stored in the field specified by Output Row Value Output.
To better understand the operation of the transformation, consider the following outline:
  • For each output row satisfying the Condition and having a nonempty Output Row Key Input:
    • Find the FIRST or LAST input row such that
      • the input row satisfies the Input Condition, and
      • the x-trackingid of the input row equals the x-trackingid of the output row, and
      • the Input Row Key Input of the input row equals the Output Row Key Input of the output row,
  • and set the Output Row Value Output of the output row to the Input Row Value Input of the input row.
Considerations for LookupRows
  • Blank key values never match anything. Even if there are input rows with blank keys and nonblank values that match the Input Condition, an Output Row Key Input of "" will always produce an Output Row Value Output of "".
  • If not forbidden by the Input Condition, a row may look up itself if its Input Row Key Input and Output Row Key Input values are the same.
If you have multiple key values, you can combine them using a Format transformation (see Format ) before applying a LookupRows transformation.
Suppose that you have a website that has a pet registration page, where the name and the breed are entered, and a later "buy toy" page where only the name of the pet is used. You would like to be able to link the pet name with the pet breed entered on the registration page. To do so, you could create the following LookupRows transformation:
Let's analyze this example using the previous outline:
  • For each output row satisfying having a nonempty value of cs-uri-query(petname) :
    • Find the LAST input row such that
      • the input row contains a nonempty value of cs-uri-query(petbreed), and
      • the x-trackingid of the input row equals the x-trackingid of the output row, and
      • the value of cs-uri-query(petname) of the input row equals the value of cs-uri-query(petname) of the output row,
  • and set the value of x-pet-breed of the output row to the value of cs-uri-query(petbreed) of the input row.
The LookupRows transformation uses the pet name (the key) to make sure that the pet breed is linked to both the pet registration and buy toy pages so that you can analyze the toys bought for each breed of pet, even for visitors with multiple pets.