Data Lookups are a crucial concept in Strategy Designer. They allow you to specify a data set that can be reused across your strategies in different ways. There are two kinds of lookups: simple and advanced.

You can create a Data Lookup by clicking the Add Lookup button above the list of lookups.

In this section:

Simple Data Lookups

Product Extension & Company Parameter Lookup

These two simple lookups can be compared to Excel’s VLOOKUP function, simply fetching a single row based on a key or a set of keys. They always require all key fields to be specified and always produce just one value. They do not allow filtering, sorting, or aggregating data.

The Product Extension and Company Parameter lookups work almost the same, with just one slight difference: the Product Extension lookup is always filtered by the SKU of the currently calculated product.

Below is an example of a simple lookup of a company parameter table called ‘AdditionalDiscount’. This table identifies each value based on three keys: Business Unit, Product Group, and Product Class. There is an input for each key that needs to be specified. Based on the specified set of the three keys, the lookup identifies a single row and returns its column called ‘Discount %’.

Open

Competition Lookup

The Competition Lookup looks into the Competition Data table and allows you to filter records based on its columns. It is always filtered for the SKU currently being calculated and always returns a price, either the highest, lowest, or an average of all the records that the lookup produces.

Notice the Filters category in the toolbox that allows you to drag & drop the filter criteria. Everything within the logical block with the ‘ALL’ option selected will be logically AND-ed together. Everything within the logical block with the ‘ANY’ option will be OR-ed together. Everything which is directly plugged into the Filter input is AND-ed together.

In the example below, the filter could be rewritten like this: Competitor = ‘MainCompetitor’ AND (Country = ‘Germany’ OR Country = 'Italy).

Open

If you need more filtering, sorting, or aggregation options, you need to use the advanced data lookup.

Advanced Data Lookup

The advanced data lookup works in a different way compared to the simple lookup. It splits the data fetching from the aggregation.

• The data fetching part is configured in the Data Lookups tab. An advanced data lookup does not produce a single value but always fetches a set of rows. You can filter and sort the result as you wish.

• The aggregation part happens in your strategy. Here you determine how to transform the rows to produce a single value.

The flexibility, however, comes with a price, and that is that the advanced data lookup is generally slower.

The advanced data lookup supports these data sources:

• Company Parameter

• Product Extension

• Product Master

• Customer Extension

• Customer Master

• Competition Data

Below is an example of the advanced data lookup. The source is a Product Extension called ‘Product Costs’. The following filter conditions are applied:

• SKU column is equal to the ID of the product currently being calculated,
  and

• Date is lower or equal to the target date,
  and

• Dependency Level Name is ‘Global’.

Additionally, the result is sorted by the Date from the highest (most recent) to the lowest (oldest).

This lookup can return zero, one, or multiple rows, as we can see in the Live Preview, and it returns all columns that are present in the table.

Because of this, we can define such a lookup once and use it multiple times in our strategies, each time querying a different column and a different aggregation function.

Using Data Lookups in Strategy

General Information

Before you can use your data lookup in a strategy, you have to save it. This is done by clicking the Save button. Even when you rename a lookup, you need to save it for the new name to be reflected in the strategies. After you save a lookup, a corresponding block will appear in your Strategies workspace in the toolbox under the Data Lookups category.

If you do not have any saved data lookup, the Data Lookups category will be empty.

Using Simple Lookups

After you save a simple data lookup, it will be available in the Data Lookups category in the strategies toolbox as a block with no configuration options. This block returns the value specified in the data lookup’s configuration.

Below is an example of a simple data lookup into the company parameter called ‘AdditionalDiscount’. This data lookup returns a ‘Discount %’. When you use the Additional Discount block in your strategy, it will evaluate the lookup based on the currently calculated product and return the found discount. You can then use it in your strategy.

Aggregating Values from Advanced Lookup

As we described earlier, the advanced data lookup does not return a single value but a set of rows with multiple columns. When it is being defined, it does not know yet what column will be used and how the values of that column will be aggregated. This is decided when the data lookup is used in a strategy. Therefore, each saved advanced data lookup creates an aggregation block that allows us to select which data lookup we want to use, which column we are going to aggregate, and which aggregation function we use.

Below is an example of several aggregation blocks, each produced for a saved advanced data lookup.

Remember the Product Costs lookup we specified earlier, which returned multiple rows? Here we can decide how to use it. For example, we can take an average of the values in the Cost column.

There are many aggregation functions available. You can reuse the same advanced data lookup multiple times, each with a different column and aggregation function.

Range Lookup

Sometimes you might want to filter a lookup based on a value that is not yet known at the time when the lookup is defined.

Example: We want to calculate a price impact based on a certain product’s weight. We store a price impact value for multiple weight ranges. Therefore we define a lookup that produces these three rows:

However, the problem is that product weight comes from another lookup and therefore cannot be directly used in this lookup’s filter.

In such a case, we can enable lookup by range for this data lookup by checking the corresponding checkbox. When we do that, we are required to specify which columns define the range. In our case it is the columns ‘Pricing Attribute Value From’ and ‘Pricing Attribute Value To’. By doing this, we made the advanced data lookup a little smarter because now it knows how to perform a range lookup.

This opens a new possibility and creates a new block type in the Data Lookups toolbox in the strategies. This block has an additional input where we can specify the value used for the range lookup. In our example, it would be the weight of the product.

Let’s say we have a Product Weight coming from a different lookup, stored in a variable. In our range lookup, we plug this variable into the block so the lookup knows which value we are comparing the range to. Do not forget to take the correct result column, which is not the ‘Pricing Attribute Value From’ but the ‘Price Impact Value’.

As you can see, the Product Weight was 120, therefore the range lookup returned a Price Impact Value of 1.1. When multiplied by the Product Cost of 12, the resulting price is 13.2.

Value Lookup

Sometimes you might want to perform a lookup not by a range, but by a single value. The setup is very similar to the range lookup, only this time you check the Enable lookup by value checkbox. When you do that, you need to specify the Lookup field which will be used for such a lookup. The Lookup field should contain unique values only.

In the example below, we have a table with a Color attribute with different colors stored in the Pricing Attribute Value column. Each color has an impact stored in the Price Impact Value column. We need to tell the data lookup that the column we want to use for the value lookup is the Pricing Attribute Value column.

When we do this and save the lookup, a new kind of data lookup block will appear in the Data Lookups category in the strategies toolbox. It works in the same fashion as the range lookup, only this time the record with the matching ‘Pricing Attribute Value’ will be picked.

Data Lookup Status

When you start using the strategies, you will notice some icons in the list of data lookups. Here is their meaning: