A Look at the Lookup Function

[Jennell_McIntire]

The Lookup function is a script function that allows you to look up and return the first occurrence of a value in a field that has already been loaded in the script.  The lookup can occur in the current table or a previously loaded table.  Here is the syntax:

lookup(field_name, match_field_name, match_field_value [, table_name])

The first parameter field_name is the field from which the returned value will come from. The field_name must be entered as a string so enclose the field name in single quotes.

The second parameter match_field_name is the field where you will be looking up the value.  This parameter also needs to be entered as a string.  This parameter and the first parameter, field_name, must be fields in the same table in order for the Lookup function to work.

The third parameter match_field_value is the value that you are looking for in the match_field_name field.

The fourth and last parameter is the table_name.  This is an optional parameter.  If the lookup is occurring in the current table that is being loaded, then this parameter can be omitted.  If the lookup is in another previously loaded table, then this parameter should be the table name enclosed in single quotes.

So, let’s take a look at a small example of the Lookup function.  In the script below, the first two inline load scripts load a ProductData and a CustomerData table.  The loading of the Temp table is where we can see the Lookup function in action.  In this example, I am looking up the customer name with a specified customer ID.  The Lookup function will return the value of the Customer field (first occurrence) from the ProductData table where the loaded CustomerID matches the value in the CustomerID field in the ProductData table.

Once this script is run, the Temp table looks like this:

The field CustomerName has the customer name that corresponded to the first occurrence of the CustomerID being loaded in the Temp table.  This value was captured by looking it up in the ProductData table.

If the Lookup function does not find a match, null will be returned.  The Lookup function is one line of code that is fairly easy to add to your script to look up a value in a field but it has some limitations.  First, the order of the search is the load order.  You are not able to sort the data so the first occurrence will be based on the load order of the value.  Second, the Lookup function is not as fast as the ApplyMap function.  While the Lookup function is flexible and easy to use once you know the parameters, ApplyMap should be your first choice when you need to look up a value based on the content of a field.  You can read more about the ApplyMap function in the blogs listed below:

Mapping … and not the geographical kind

Don't join - use Applymap instead

Thanks,

Jennell