PsiBoxIterator(table_range)
A box iterator establishes a loop within a box function. In earlier editions of Analytic Solver, loops were confined to usage within VBA or, more recently, with Python in Excel, both inaccessible to the PsiInterpreter during Problem Setup. This constraint can now be circumvented by introducing a box iterator.
Use the PsiBoxIterator function to execute the box iterator. The PsiBoxIterator function must be the only Psi function contained in the cell. Multiple PsiBoxIterator functions may be contained within the same worksheet. Pass the address of the box iterator function starting with the cell containing the syntax and ending with the bottom right most cell containing the return formula. In the screenshot below, notice that the PsiBoxIterator function in cell E20 includes the cell address H15:I20.
More information:
A box iterator contains many of the same components of a box function, an example of box iterator is shown below.
Example 1: Box Iterator Example using Excel Syntax
- The (required) name of the box iterator appears above the top-left cell of the table layout. In this example, the name of the box iterator is “Name”.
- The (required) formula syntax (FEEL or EXCEL) is passed in the top upper left hand corner. If syntax is FEEL, syntax must be compliant with DMN decision modeling specifications. In this example, the formula syntax is “Excel”. Note: Box functions defined in FEEL syntax are limited to the internal box scope. Variables or defined names may not be referenced outside of the box iterator.
- The (optional) returned value type is specified to the right of the formula syntax. In this example, the returned value is “number”.
- Below the formula syntax is the (required) loop type, in this case the loop type is “for”. Analytic Solver supports three loop types: for, every, and some. In this example, the loop type is “for”.
- A “for” loop behaves the same in a box iterator as in a programming language, i.e a condition is specified and the loop continues until the condition is no longer satisfied, changing the formula on each loop.
- An “every” loop checks whether all elements in the loop are True. Computed result must be a single Boolean value.
- A “some” loop checks whether at least one computed element of the loop is True. Computed elements must be Boolean.
- Loop index (required) specifies the variable that will be changing. In this example, the loop index is “i”.
- Below the loop type, the (optional) operator “in” (short for input) appears.
- To the right of “in”, the (required) index ranges are listed for each index either as a list/array or through the range operator “…”. In this example, the list or array is “1..rows(amount)” indicating from “1 to the number of rows in the Excel defined name, amount.”
- The (optional) iterator body of formulas appears. This body is composed of both columns. The first names the formula and the 2nd contains the formula itself.
- The (required) last row specifies the result of the iterated formula. The first column names the result and the 2nd column calculates the result.
For more information, see the Box Iterator section within the Analytic Solver User Guide and/or the Analytic Box Iterator Example at Help – Example Models – Decision Table and Decision Tree Examples for an illustration.