Lookups with multiple key columns

1. Lookups with multiple key columns

Welcome back. In this video we'll experiment with lookups that require more than one key column to function properly, and how to identify those scenarios.

2. Lookup behavior with multiple matches

In the last video we learned how Sigma lookups display a ‘multiple values’ message when a single record receives more than one match in a second table.

3. Insert title here...

So far, we've worked on examples where this behavior was expected, and the data just needed some aggregating. For example, when we looked up customer balances we used an aggregation any time a customer had more than one account.

4. Carefully consider key columns

But sometimes, we do not expect our lookups to return multiple values. Perhaps if we were looking up something like a customer’s ID number or birth date, we’d expect that only one record returns for each customer. When we receive multiple matches unexpectedly, its often because our lookup key columns are not specific enough for the situation. Some lookups require not one, but two or more key column pairs to work as expected. Let’s walk through an example of this with our Oakmark Bank data.

5. DEMO

Let's imagine that Oakmark Bank is trying to add the column [Credit Score] to their Customers table. In the Customers table, we have fields for the customer first and last name, email and several demographic fields. The Credit Scores table has customer first and last name and email, and also the individual's credit score. Let's begin setting up the lookup. We'll point to the credit score table and ask the lookup to return the credit score field. To map the elements with key columns, let's see what happens if we only select customer first name as the key column. Obviously we have several repeating first names in our data, so this lookup result isn't ideal. One person can't have multiple credit scores, so let's revisit. The Add Lookup modal has a preview at the bottom which will show you how many of your records from your first table are receiving multiple results from the second table. In a situation like this where we're expecting a one-to-one cardinality, we'd like this second bar to be empty. Let's add customer last name as a second key column. Additional key columns can be added with the '+add another mapping' button. Looks better, but we can tell just from this preview that we're still likely to get multiple matches. Let's add a third identifying field, customer email. This addition finally gives us a one-to-one match. Sigma's ability to perform multiple key mappings helps us ensure we can perform lookups even when tables don't have a single master ID column to work with. Keep in mind that choosing the incorrect key columns or too few key columns to map with can result in poor results in Sigma lookups. Sigma developers should carefully examine their data's granularity and ensure data is properly cleaned and prepared before attempting lookups.

6. Let's practice!

Now its your turn. Finish up this chapter on lookups with some hands-on practice configuring multiple key mappings for Oakmark Bank.

Create Your Free Account

By continuing, you accept our Terms of Use, our Privacy Policy and that your data is stored in the USA.