Add Lookup modal

1. Add Lookup modal

Welcome back. In this chapter, we'll focus on Lookups in Sigma. They're an easy way to bring columns from multiple table elements together.

2. Bringing tables together in Sigma

Sigma allows us to join data from different tables together in 3 main ways - Unions, Joins and Lookups. Unions stack rows from multiple tables. They're like copying and pasting rows from one table onto another table.

3. Bringing tables together in Sigma

Joins and Lookups add columns from one dataset onto another. They're similar to SQL joins or VLOOKUP functions in Excel. Joins can leverage complex logic to bring together data from 2 or more tables. Lookups are different - they're built for simple logic and speed.

4. Oakmark Bank needs your help

Let's talk about a scenario at Oakmark Bank to learn how lookups work. Imagine a Manager at Oakmark Bank needs to contact all customers holding certificates of deposit, also called CDs. They need a list of customers with CDs and their contact information. They have a list of all open CD accounts in a CDs table, but customer names and emails are stored in a separate Customers table. Sigma's lookup functionality will allow us to bring data from the Customers table onto the CDs table.

5. Two ways to build lookups in Sigma

We could build the lookup with formulas, or with a guided Add Lookup modal. We'll focus on the Add Lookup modal for now.

6. Lookups are limited to one or two tables

Sigma lookups have three requirements before they can be configured. First, lookups cannot work with more than two tables at a time. Both table elements must be present in your workbook and sourced from the same connection. It's also possible to perform a lookup with a single table, where the table looks up values in itself; we'll cover this feature in a later video.

7. Lookup tables must share a key column / columns

Second, the two tables must share a key column. The key columns must contain data such that a row from table A could be matched to a row in table B with the same key column value. Key columns are often ID numbers, but they can be anything, perhaps a product ID, record number, or even a unique string of text. The key columns do not need to have the same name, but they must be of the same column type. Sigma developers should carefully consider which columns to use as key columns. Choosing too few key columns, or key columns that are not specific enough for the use case, can lead to unexpected lookup results.

8. Cardinality impacts Lookup results

Third, lookups in Sigma are best suited for one-to-one or many-to-one lookups. This is called the cardinality of the lookup and it refers to how many lookup values you expect to find for each row from the first table in the second table.

9. CDs to Customers - what's the cardinality?

In this example, Customer IDs may appear more than once in the CDs table, as customers might own multiple CDs. In our customers table, however, each customer's ID only appears one time,

10. CDs to Customers - what's the cardinality?

so we're doing a many-to-one lookup here. Sigma developers should think carefully about the structure of their data when performing lookups. Tables often have unique ID fields which are enough to ensure 1-to-1 matches in many cases, but it will depend on your situation.

11. Let's practice!

Take a moment to test your knowledge of lookups in Sigma. In the next video, we'll configure a lookup and talk about the results.

Create Your Free Account

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