Aggregating lookup results
1. Aggregating lookup results
Now that we’ve built some simple lookups, in this video we’ll explore a more complicated scenario. A Loan Officer at Oakmark Bank is reviewing loan applications. As they review each application they can see the length of the loan the customer has requested. They’d like some summary information next to this column showing the average term length requested for this type of loan across all applications. For example, this customer is requesting a personal loan with a term of 2 years – is this typical for other personal loan requests in this table? We could set up some table summaries on the bottom of the table using AverageIf to help with this, but there’s another option using an aggregated lookup. We’ve seen how lookups work when connecting 2 tables; but lookups can also be applied within a single table, adding aggregations to each row based on another column in the data. This provides useful context without needing to scroll down to a table summary at the bottom of the table. Let’s try a single table lookup using aggregation to add average loan lengths next to each application. We’ll add a new column via lookup next to the [Requested Term Years] column. We’ll reference this same table as our lookup, and add the [Requested Term Years] column. We’ll aggregate to return an average of term length. For now this is an average across all rows and types of loans in the table, but setting the key column to [Loan Type] limits the lookup to loan requests of the same type. Clicking Done, we now see that on average, personal loans are requested for a term of 2 and a half years – quite close to this customer’s ask of 2 years. Sometimes it's difficult to know when to use aggregation during a lookup. Let’s look at one more example where it's useful. Here we have a table of Oakmark Bank customers and a table of all accounts. Let’s imagine we want to look up each customers’ total balance across all accounts onto this table of customers. Because many customers hold multiple accounts at Oakmark Bank, this means we’re attempting a one-to-many lookup. One record from the first table could result in many matching results from the second. Let’s proceed anyway and see how Sigma behaves. Adding a lookup column to the Customers table, we’ll reference the Accounts table, bring on the Balance and use Customer ID as the key column Already we’re getting a warning that many of the customer IDs have multiple matches in the accounts table, but let’s click Done and see the results. For any customer with multiple accounts, we don’t see the total balance, we see the words ‘multiple values’. This text is not clickable, so we don’t have much useful information here. To see customers’ summed balances, we could edit the lookup to aggregate the balance column with Sum, and we’d see our desired result. We’ve seen that aggregation in lookups can serve multiple purposes. It's useful in single table lookups and in cases of one-to-many cardinality, where multiple lookup matches might exist for a single record. In the next exercises you’ll practice aggregated lookups to help Oakmark Bank organize their customer data.2. Let's practice!
Create Your Free Account
or
By continuing, you accept our Terms of Use, our Privacy Policy and that your data is stored in the USA.