Multi-Column Lookup Function
Purpose
To perform a multi-dimensional matching value lookup on a pre-defined lookup table.
Type Name
‘lookup_multicolumn’
Definition Syntax
[lookup_set_key] : [lookup_key_field] : [row_key]: [column_key] : [fall_through_key_or_value]
where:
- ‘lookup_set_key’ is the key used to identify the lookup set in the Juno Cassandra multi-column lookup sets. For the desktop version, this should match a sheet name in the specified multi-table lookup file (Excel file. Lookups assumed to be on sheets starting with ‘lkp_’).
- ‘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 specified row_key column.
- ‘row_key’ is the name (case-sensitive) of the column against which the value mapping to the ‘lookup_key_field’ will be mapped. Values in this column must be unique (no duplicates allowed).
- ‘column_key’ is the name (case-sensitive) of the column in which the values to be looked up are. This can be a literal column name, or else a key mapping to a value in the value dictionary.
- ‘fall_through_key_or_value’ is the key column’s values 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 1
For this example, we assume your Juno Cassandra model contains a set of multi-column lookup tables, and the one of these tables has a key named ‘lkp_AADI_all_cracks’. We assume that the table mapping to this key looks as follows:
Let’s assume we want to add a JFunction that will use the values for the parameter called ‘par_SurfType’ in the lookup table’s column ‘Surfacing’. Furthermore, we want to look up the value in the column named ‘traff_high’. For this case, we will need to specify the setup code for a Multi-Column lookup JFunction as follows:
‘lkp_all_cracks : par_SurfType : Surfacing : traff_high’
Note that our setup code is delimited with a colon ‘:’ and contains four values:
- The first value is ‘lookup_set_key’. That is, the key that identifies the specific lookup table amongst all others.
- The second value is ‘lookup_key_field’. This is the key that identifies the model parameter or raw data field that contains the value we want to look up. In this case, we want to look up the value mapped to ‘par_SurfType’.
- The third value is the ‘column_key’. This is the column in which the key-matching must occur. In this case, it is the left-most column called ‘Surfacing’.
- The fourth value is the name of the column in which we want to look up the value to be returned. In this example, this is column ‘traff_high’.
The figure below explains the setup code schematically:
Note that in this example no fall-through value is provided. Thus, if the value in parameter ‘par_SurfType’ has a value such as ‘Concrete’ that is NOT found in the ‘Surfacing’ column, then an error will be thrown at runtime since no fall-through value is provided.
Note that all values provided in the setup code are pressumed to be case-sensitive.
Example 2
Let us now expand our example in two ways:
First, we want to provide a fall-through value to handle a case where the value in variable ‘par_SurfType’ does not match any of the values in the ‘Surfacing’ column. Let’s assume our fall-through value is ‘Asphalt concrete’, which means if no matching value is found, the row in which lookup occurs will be the one matching key value ‘Asphalt concrete’.
Secondly, instead of hard-coding the name of the value in which to look up (i.e. column ‘traff_high’ in the above example), we want the lookup column to be determined at run-time. Specifically, let us assume that the column in which we want to look up will be determined by the value mapping to key ‘traffic_class’.
To apply the above modifications, our setup code will change to:
‘lkp_all_cracks : par_SurfType : Surfacing : traffic_class : Asphalt concrete’
The figure below explains how this code will be parsed:
Two important things to note from the above example:
The value supplied as a fall-through value is not the value that will be returned by the JFunction, but rather the row key that determines which row is used for the lookup. The fall-through value must match exactly (case-sensitive) one of the values in the key column, otherwise an error will be thrown.
The values held against the key ‘traffic_class’ in the value dictionary must match (case-sensitive!) one of the column names in the lookup table. Thus, for the example above, the parameter or function value mapping to key ‘traffic_class’ must be either ‘traff_low’, ‘traff_med’ or ‘traff_high’.