Nested tables are data tables that are referenced within another data table. Nested tables provide flexibility to dynamically select the table to be used as input at runtime based on other model inputs.
Setting Up Nested Table Structures
The first step in creating nested tables is to determine the Table Structure for the nested table to be referenced. In order to ensure data is being used correctly, SLOPE requires that a nested table conforms to a single Table Structure for each type of nested reference. In this example, the Index for Lapse Rate Table is Duration and the Data Column returned is the Lapse Rate.
The next step is to add a new column to the outer lookup Table Structure that points to the referenced Table. To do this, add a new Data Column to the Table Structure. Select 'Data Table' as the Data Type and then select the appropriate Table Structure that you want to reference.
In this example, Lapse Rate Table Lookup has an Index of Plan Code and a Data Column named Lapse Rate Table with Data Type equal to Data Table, pointing to a Lapse Rate table type.
Create Data Tables
Once the Data Structure is defined, a set of Data Tables can be created through various means including importing data and manual entry. The name of each table that will be referenced within the model is entered into the Description field.
Once the tables are created the individual tables are displayed in the interface, as shown below. These are all the Data Tables that can be referenced as nested tables.
Create Links to Nested Tables
Next, a data table is created with the Table Structure that will be referencing these nested tables. In the editor, the column with the Data Table data type will be editable as a dropdown. The available options within this dropdown will be made up of all the Data Tables that exist of the given Table Structure type. For each row within this table, make a selection of the nested table to be referenced.
Using Nested Tables in Formulas
Now that nested table structures and tables have been created for our model, the nested tables can be used in variable formulas in the model. The first step is to define a variable that will read in the nested table to be referenced.
Following the example in this article, a new variable named Lapse Rate Table can be created within the model. This variable is created by first clicking on Add New above the list of variables. When the Create New Variable window appears, the Data Type is set equal to Data Table. The Table Structure input should be set to to the nested table Table Structure. This is the table reference that will be returned by the output table lookup reference.
Once the new variable is created, the Formula Editor is pre-populated with the following:
The box to the right of Structure is then selected from the output table reference. And the Value should be set to the name of the column that contains the nested table reference. The lookup values for the indexes defined on your outer table should be set as needed for your particular lookup.
The final step is to use this dynamic table reference to read data from the nested table. In a different variable, create a Table Lookup formula that references the nested Table Structure. Now that another variable exists on the same Product/Portfolio/Company with a Data Table type, it will become available as an option under the Data Table dropdown on the Table Lookup formula.
The Default option means to use the Data Table that is set on the Projection Inputs. The other options in this list will be other variables that return a data table, meaning the nested table. Select the variable that provides the needed tables. Set the rest of the table index values as needed for the type of table you are working with.
In this example, the annual lapse rates assumed in the model are based on the duration (policy year) calculated for a model point at each time index. These values are being looked up from a lapse rate table which is based on the Plan Code within the Model Point File.