Lookup-Number
Purpose
To perform a one-dimensional matching value lookup on a pre-defined lookup set using a numeric key set ranked from low to high. This function is similar to Excel’s VLookup function with a minor difference as explained in the example below.
Type Name
‘lookup_number’
Definition Syntax
[lookup_set_key] : [lookup_key_field]
where:
- ‘lookup_set_key’ is the key used to identify the lookup set in the Juno Cassandra model setup.
- ‘lookup_key_field’ is the key to extract the value from the value dictionary. This value will then be used to perform the lookup in the designated lookup set.
For the JFunction Number Lookup to work, you should ensure that the values held in the ‘setting_key’ column of the lookups table are all numeric values in increasing order. If your values are not numeric an error will be thrown. If they do not increase from lowest to highest, the incorrect result will be returned.
The Number Lookup JFunction is similar to the Excel’s Approximate VLookup function, except that values that fall below the minimum value will return the lookup value for the lowest value, whereas Excel will return ‘#N/A’ in such cases. See the example below for details.
Example
For this example, we assume your Juno Cassandra model setup file has on the mandatory sheet ‘lookups’ the following table:
As you can see from the above figure, there are three ‘lookup sets’ with names/keys: ‘general’, ‘lookup_class’ and ‘defaults’.
Now, given the above setup for our JCass lookup sets, we can define a JFunction Number Lookup as follows:
‘lookup_class : f_distress_perc : ac_undefined’
This function definition instructs JCass to do the following:
- Use the lookup set with name/key = ‘lookup_class’
- Extract the value matching key ‘f_distress_perc’ from the value dictionary, and
- Return the value in the lookup set that matches the value in ‘f_distress_perc’.
So, for example, if the value matching key ‘f_distress_perc’ for the current element is 14, then this JFunction will return 2 because 14 lies below the 20 limit and at or above the 10 limit, and the value matching the LOWER limit is always returned (similar to Excel’s Approximate VLookup).
If the value matching key ‘f_distress_perc’ for the current element is -1, then this JFunction will return 1 because -1 lies below the lowest limit (Excel’s approximate VLookup will return ‘#N/A’ in this situation).
If the value matching key ‘f_distress_perc’ for the current element is 87, then this JFunction will return 5 because 87 lies at or above the final limit of 85.
As a final example, if the value matching key ‘f_distress_perc’ for the current element is 50, then this JFunction will return 4 because 50 lies at the limit 50 mapping to value 4.