loopTable¶
Like TableLookup, this looks up values from a table in the model, but instead of
looking up a specific row, it causes the entire model to be executed once for every table row. At each iteration, this
function returns a different value, depending on the current row.
loopTable(
tableName,
columnName=varName,
)
This function was previously called TableLookup. (note the .).
Arguments¶
tableName- The name of the table to loop over (e.g.
"t_ModelPoints"). columnName- The name of the column whose value to return at each iteration step. Defaults to the name of the variable that uses this function.
Basic example¶
Here is a variable set:
| VariableSetName | VarName | Formula | Comment |
|---|---|---|---|
| MyVariableSet | VarA | loopTable("MyTable", "ColOne") | This will have a different value for each iteration. |
| MyVariableSet | VarB | 5 | This will have the same value for each iteration. |
This will cause the model to be executed N times, with the following values:
| Iteration | VarA | VarB |
|---|---|---|
| 1 | MyTable Row 1 ColOne | 5 |
| 2 | MyTable Row 2 ColOne | 5 |
| … | MyTable Row … ColOne | 5 |
| N | MyTable Row N ColOne | 5 |
Using multiple columns¶
You may use the loopTable function with the same tableName and different columnName in multiple places in the
variable library. Say you have a table called MyTable with N rows:
| VariableSetName | VarName | Formula | Comment |
|---|---|---|---|
| MyVariableSet | VarA | loopTable("MyTable", "ColOne") | This will cause the model to be executed once for every row in MyTable, i.e. N times. At each iteration, VarA will contain a value from ColOne. |
| MyVariableSet | VarB | loopTable("MyTable", "ColTwo") | This will reuse the same loop, i.e. the model will still be executed N times, and not N*N times. At each iteration, VarB will contain a value from ColTwo. |
| MyVariableSet | VarC | loopTable("MyTable", "ColThree") | Ditto. At each iteration, VarC will contain a value from ColThree. |
This will cause the model to be executed N times, with the following values:
| Iteration | VarA | VarB | VarC |
|---|---|---|---|
| 1 | MyTable Row 1 ColOne | MyTable Row 1 ColTwo | MyTable Row 1 ColThree |
| 2 | MyTable Row 2 ColOne | MyTable Row 2 ColTwo | MyTable Row 2 ColThree |
| … | MyTable Row … ColOne | MyTable Row … ColTwo | MyTable Row … ColThree |
| N | MyTable Row N ColOne | MyTable Row N ColTwo | MyTable Row N ColThree |
Aggregation¶
Whenever loopTable is used, and the model has more than one iteration, it's probably useful to see the aggregated
data.
Configuring aggregation¶
It's possible to set an aggregator method for each variable in the Variables table. To add the ResolveAggregation
column to the Variables table, run the activateResolveAggregation macro in your model workbook (use Alt + F8 to
see a list of macros).
The following aggregators are available:
Ignore- Omit the variable from the aggregated results.
Sum- Sum the value over all iterations.
MeanorAverage- Take the mean over all iterations.
Max- Take the maximum value over all iterations.
Min- Take the minimum value over all iterations.
First- Take the value from the first iteration.
Last- Take the value from the last iteration.
Aggregated results¶
The output workbook will contain a table with the results of one iteration (called ProjArea_Iteration when using the
VBA engine), and another table with the aggregated results (called ProjArea when using the VBA engine). You may select
which iteration to view.
Coming soon
Link to documentation about the structure of the output workbooks.
Using multiple, nested loops¶
Coming soon
This feature is in development, and not available to all users yet.
When looping over a single table, the model loops over a one-dimensional domain of input values. Sometimes, you would
want to loop over two or more dimensions. Say you have three tables, called XValues (having 1 row), YValues (having
2 rows) and ZValues (having 3 rows). Each table represents a three-dimensional domain of input values. It is possible
to run your model for every point in the cartesian product of these
three tables. The variable library might look as follows:
| VariableSetName | VarName | Formula | Comment |
|---|---|---|---|
| MyVariableSet | VarX1 | loopTable("XValues", "A", …) | Defines the first loop. Since there is only one row in XValues, the model will run once. |
| MyVariableSet | VarX2 | loopTable("XValues", "B", …) | Reuses the first loop. |
| MyVariableSet | VarY1 | loopTable("YValues", "A", …) | Defines the second loop. Since there are two rows in YValues, the model will run 2 times for every existing iteration (1*2=2). |
| MyVariableSet | VarY2 | loopTable("YValues", "B", …) | Reuses the second loop. |
| MyVariableSet | VarZ1 | loopTable("ZValues", "A", …) | Defined the third loop. Since there are three rows in ZValues, the model will run 3 times for every existing iteration (123=6). |
| MyVariableSet | VarZ2 | loopTable("ZValues", "B", …) | Reuses the third loop. |
This will cause the model to be executed 1*2*3 = 6 times, with the following values:
| Iteration | VarX1 | VarX2 | VarY1 | VarY2 | VarZ1 | VarZ2 |
|---|---|---|---|---|---|---|
| 1 | XValues Row 1 Col A | XValues Row 1 Col B | YValues Row 1 Col A | YValues Row 1 Col B | ZValues Row 1 Col A | ZValues Row 1 Col B |
| 2 | XValues Row 1 Col A | XValues Row 1 Col B | YValues Row 2 Col A | YValues Row 2 Col B | ZValues Row 1 Col A | ZValues Row 1 Col B |
| 3 | XValues Row 1 Col A | XValues Row 1 Col B | YValues Row 1 Col A | YValues Row 1 Col B | ZValues Row 2 Col A | ZValues Row 2 Col B |
| 4 | XValues Row 1 Col A | XValues Row 1 Col B | YValues Row 2 Col A | YValues Row 2 Col B | ZValues Row 2 Col A | ZValues Row 2 Col B |
| 5 | XValues Row 1 Col A | XValues Row 1 Col B | YValues Row 1 Col A | YValues Row 1 Col B | ZValues Row 3 Col A | ZValues Row 3 Col B |
| 6 | XValues Row 1 Col A | XValues Row 1 Col B | YValues Row 2 Col A | YValues Row 2 Col B | ZValues Row 3 Col A | ZValues Row 3 Col B |
Controlling the order of loop nesting¶
Coming soon
This feature is in development, and not available to all users yet.