lookup command overview

The SPL2 lookup command enriches your source data with related information that is in a lookup dataset. Field-value pairs in your source data are matched with field-value pairs in a lookup dataset. You can either append to or replace the values in the source data with the values in the lookup dataset.

Syntax

The required syntax is in bold.

lookup <lookup-dataset> (<lookup-field> [AS <event-field>] )...

[ (OUTPUT | OUTPUTNEW) ( <lookup-destfield> [AS <event-destfield>] )...]

How the SPL2 lookup command works

The following lookup dataset, named products, contains product information and prices for a set of board and card games. For example:

product_id	product_name	price
DB-SG-G01	Mediocre Kingdoms	24.99
DC-SG-G02	Dream Crusher	39.99
FS-SG-G03	Final Sequel	24.99
WC-SH-G04	World of Cheese	24.99

The events contain the field productID. A search was run to summarize the total number of purchase transactions, the total number of products purchased, and the product IDs. The results are organized by ipaddress.

This is a sample of the search results. The products are identified by the productID.

ipaddress	total_purchases	total_products	productID
107.3.146.207	72	3	DB-SG-G01 FS-SG-G03 WC-SH-G04
128.241.220.82	95	2	DB-SG-G01 DC-SG-G02
194.215.205.19	60	4	DB-SG-G01 DC-SG-G02 FS-SG-G03 WC-SH-G04
211.166.11.101	91	2	DB-SG-G01 WC-SH-G04
87.194.216.51	134	3	DC-SG-G02 FS-SG-G03 WC-SH-G04

You can use the lookup command to lookup the product_id in the products dataset, match that with the productID in the events, and return the product_name.

...| lookup products product_id AS productID OUTPUT product_name

The results would look like this:

ipaddress	total_purchases	total_products	productID	product_name
107.3.146.207	72	3	DB-SG-G01 FS-SG-G03 WC-SH-G04	Dream Crusher Final Sequel World of Cheese
128.241.220.82	95	2	DB-SG-G01 DC-SG-G02	Mediocre Kingdoms Dream Crusher
194.215.205.19	60	4	DB-SG-G01 DC-SG-G02 FS-SG-G03 WC-SH-G04	Mediocre Kingdoms Dream Crusher Final Sequel World of Cheese
211.166.11.101	91	2	DB-SG-G01 WC-SH-G04	Mediocre Kingdoms World of Cheese
87.194.216.51	134	3	DC-SG-G02 FS-SG-G03 WC-SH-G04	Dream Crusher Final Sequel World of Cheese

See also

lookup command

lookup command syntax details

lookup command usage

lookup command examples