Be on the lookout for lookups
In the fourth installment of our series on Monarch’s calculated fields, today we’ll have a look at lookup fields. We’ll talk about what’s different about lookup fields compared to the other calculated fields and how we can enhance our models by utilizing them.
A lookup field lets us add a specific value to the model based on every occurrence of another specific value. You must define the field type of the lookup field itself (character, date/time, numeric or memo), but this is not dependant upon the type of field that the lookup is based upon. That is to say that you can mix and match field types here: if you want a numeric lookup based on a character field, no problem.
It might be easier to briefly consider an example rather than discussing theory.
Let’s say that we want to assign a priority code number to a set of customer names. We would create a lookup field whose input field is CustomerName, and we would assign our best, most regular customers a priority 1, the customers we see sporadically a level 2, and those we only hear from once in a while a level 3 code.
Hopefully this already sounds like lookups could be a useful feature to you. But how do actually do this?
It’s really very simple. First you begin defining a new calculated field in the Table window. Select Lookup, and type a name for the field and select the field type, and optionally tune the other values as you see fit. Then on the Lookup tab, select the input field. This is the field extract from the table that you want to base your new values on. The easiest thing to do next is click the Get Input Values button. This populates the grid with a list of all of the unique values for that field in your current data. For smaller data sets, this process is nearly instantaneous. Larger data sets might take a few seconds.
Simple Isn’t Always Easy
The next step is where a bit of work comes in. For each Input Value listed in the grid, we must supply an Output Value. For longer lists, this could be a significant task.
If there other Input values that you want to plan for in advance, you can click the Add button and manually supply those additional Input values, and their corresponding Output values. The Input values that were initially listed are based solely on your current data set, so if you know of other specific values that may appear in a future data set, you may as well prepare for them now, seeing as you’re in the thick of it now anyway.
Of course you could revisit the lookup field each time you use this model on new data, just to make sure you’re capturing all of the Input values.
Lookup tables such as that in this grid are sometimes referred to as mapping or translation tables. Each input value translates into a specific output.
Where a formula based calculated field can generate a myriad of outputs for an input field based on the rules we build into our expression and changing values coming into the expression, lookup fields are very static creatures. We must manually supply the output for a given input. Because of this, lookup fields are best suited for those times when we need a more subjective eye. We know our data best so we can setup the rules better than any formula we can compose.
Assuming that you know your customers reasonably well, only you can say which customer would be at which priority level easily. Yes, if we want to make our model more sophisticated we could base that priority level on transaction frequency, or profitability, or sales volumes, or some other criteria that would lend itself to a quantifiable qualification.
Maybe we need to derive general regional information (east, west, northeast) based on the specific location. Perhaps it’s a date that’s generated based on a promotion code. There are countless applications for Monarch’s lookup fields.
What’s the Downside?
As useful as lookup fields are, and they really are, there are negatives:
- You can only easily edit the output values within the Monarch model. To revise the list, you must have Monarch installed, and know how to edit the list.
- If you want to be sure that you’ve listed all possible input values for the data set, you’ll likely want to check or revise the output values every time you use your model.
These may not be significant issues if the input field doesn’t frequently create new input values in your particular data set.
When it comes to using lookup fields, it’s your knowledge of how to add real value to your models while simultaneously minimizing any possible problems that might get introduced that will help you excel with Monarch.
