Lookup Function
Purpose
To perform a one-dimensional matching value lookup on a pre-defined lookup set.
Type Name
‘lookup’
Definition Syntax
[lookup_set_key] : [lookup_key_field] : [fall_through_key_or_value]
where:
- ‘lookup_set_key’ is the key used to identify the lookup set in the Juno Cassandra model setup file.
- ‘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.
- ‘fall_through_key_or_value’ is the key mapping to the value dictionary that identifies the value to return if the lookup value is not found in the lookup set. This is an optional value.
If no fall_through value is provided, then JCass will throw an error if the value mapping to the lookup field key is not found in the lookup set.
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: ‘objective’, ‘traffic’ and ‘surf_life_exp’.
Now, given the above setup for our JCass lookup sets, we can define a JFunction Lookup as follows:
‘surf_life_exp : f_exp_life_code : ac_undefined’
This function definition instructs JCass to do the following:
- Use the lookup set with name/key = ‘surf_life_exp’
- Extract the value matching key ‘f_exp_life_code’ from the value dictionary, and
- Return the value in the lookup set that matches the value in ‘f_exp_life_code’.
So for example, if the value matching key ‘f_exp_life_code’ for the current element is ‘R_2_1CHIP’, then this JFunction will return 11.2. If the value in ‘f_exp_life_code’ is not found in the lookup table, then the value in the value dictionary that matches key ‘ac_undefined’ will be returned since this is the fall-through key/value.
Comment
The JFunction Lookup mimics the ‘VLOOKUP()’ function in Excel. However, the JFunction lookup will only work for exact matches. If you want to do a range lookup on numerical values, then we recommend you use the ‘Multiple-If’ function.