Lookup

Example

Our company's marketing team captures demographic data in an external data source for opportunities stored in Salesforce. To help create more targeted campaigns, the team is building a dashboard that segments our current opportunities by the customer demographics. Let’s use a lookup to add the demographic data to each opportunity record.

Consider the following two data streams. The left data stream contains opportunity records. The right contains demographic data. To illustrate how the lookup handles special cases, both data streams contain unmatched rows and duplicate keys.

If the lookup is configured to return a single match when multiple matches are found, the lookup produces the following results based on the matching keys.

All rows from the left, including rows without a match, are included in the results. Although Cust_ID 1 has two matches on the right, the lookup returns only the first matching record. Also, because Cust_ID 4 doesn’t have a match, the Cust_id and Education Level columns are null and Average Income is 0 for that record. Unmatched dimensions are set to null. Unmatched measures are set to 0.

If the lookup is configured to return all matching values when multiple matches are found, the lookup produces the following results based on the matching keys.

A multiple-match lookup returns the same results as the single-match lookup, except for left rows that have multiple matches. Unlike a single-match lookup, a multiple-match lookup returns all matches and combines them. For dimension columns, it generates a multivalue column with all dimension values—notice how High School and Secondary School are both in the Education Level column for Cust_ID 1. For measure columns, it adds the measure values from all matched records. For Cust_ID 1, the Avg Income column is 98,000 (50,000 + 48,000).

Note The Preview tab doesn’t show all values in a multivalue column. Instead, it shows the first value only. Keep this behavior in mind when you preview the results of a multiple-match lookup. In the example, notice that the Preview tab shows only High School in the Education Level column even though the column contains High School and Secondary School.