FormulaIterator Class

Example 1

The total_cash formula in this example is built based on the example published in the following article. Let's assume that we have an initial cash value (the initial_cash) of $10,000 and want to invest this money in a cash deposit (CD) account. Let's also assume a monthly interest rate (the interest_rate) of 1.25%. To calculate the total value at the end of the 24th month (the total_cash), we're going to calculate the principal for each month using the VALUE() function, and add the interest to the previous month. The NEXT() function retrieves the loop variable. In this example the condition NEXT()=0 is true only at the beginning of the formulaIterator loop. At this time its loop variable is equal to the loop initValue and the value of the total_cash is set to the initial_cash value:

total_cash=ROUND(IF(NEXT()=0, initial_cash, VALUE() * (1 + interest_rate)), 3)

import com.crystalprism.ce.formula.HybridFormulaEvaluator;
import com.crystalprism.ce.usermodel.*;

public class FormulaIteratorExample1 {
    public static void main(String[] args) {
        String myFormulaText = "ROUND(IF(NEXT()=0, initial_cash, VALUE() * (1 + interest_rate)), 3)";
        FormulaField myFormulaField = new FormulaField(ComputeFieldType.NUMERIC, myFormulaText);
        double initValue = 0, increment = 1, maxValue = 24;
        FormulaIterator formulaIterator = new FormulaIterator(initValue, increment, maxValue, LoopOperator.VOID);
        CalculationContext calculationContext = new CalculationContext("FormulaIteratorExample1");

        calculationContext.put("interest_rate", new InputField(ComputeFieldType.NUMERIC, 0.0125));
        calculationContext.put("initial_cash", new InputField(ComputeFieldType.NUMERIC, 10000));
        calculationContext.put("total_cash", myFormulaField);

        myFormulaField.setFormulaIterator(formulaIterator);
        HybridFormulaEvaluator.evaluateFormula("total_cash", calculationContext);
        System.out.println("Total cash at the end of the 24th month: " + myFormulaField.getDisplayValue());
    }
}

Total cash at the end of the 24th month: 13473.511

Example 3

In this example the first column of the the TableArray my_table contains the amounts which correspond to fruit names in its second column. The expression in this example iterates over these amounts using the NEXT() function. Each iteration invokes the VLOOKUP() function to retrieve the corresponding fruit name and pass it to the formulaIterator's LoopOperator.CONCAT. At the end of the the loop initiated by the formulaIterator the expression calculated value will contain all retrieved fruit names concatenated in a text string:

VLOOKUP(NEXT(), "my_table", 2)

import com.crystalprism.ce.formula.HybridFormulaEvaluator;
import com.crystalprism.ce.usermodel.*;

public class FormulaIteratorExample3 {
    public static void main(String[] args) {
        Object[][] tableArrayBase = {{10, " Apple "},
                                     {30, " Pear "},
                                     {50, " Orange "},
                                     {70, " Banana "}};
        TableArray tableArray = new TableArray(tableArrayBase);
        String myExpressionText = "VLOOKUP(NEXT(), \"my_table\", 2)";
        FormulaField myExpressionField = new FormulaField(ComputeFieldType.ALPHANUMERIC, myExpressionText);
        CalculationContext calculationContext = new CalculationContext("FormulaIteratorExample3");
        double initValue = 10, increment = 20, maxValue = 70;
        FormulaIterator formulaIterator = new FormulaIterator(initValue, increment,  maxValue, LoopOperator.CONCAT);

        myExpressionField.setFormulaIterator(formulaIterator);
        calculationContext.put("my_table", tableArray);

        HybridFormulaEvaluator.evaluateExpression(myExpressionField, calculationContext);
        System.out.println("Calculated result: " + myExpressionField.getDisplayValue());
    }
}

Calculated result:  Apple  Pear  Orange  Banana

Example 5

This example shows a simplified version of the recursive Lambda function REPLACECHARS() published on our website. This version is not recursive. It iterates over the list of "illegal" characters (in our example '1', '2', and '3') to be removed using the FormulaIterator functionality. At the beginning of the formulaIterator infinite loop the IFS statement sets the my_formula field value to the text string of the input field text_string. Then the IFS statement provides that if the NEXT() function value is less than or equal to the length of the illegal_chars text string, the next "illegal" character will be removed from the current approximation of the my_formula text string by the SUBSTITUTE() function using the VALUE() function as an argument, and otherwise invoke the STOP() function to break out of the loop with the expected result:

my_formula=REPLACECHARS(text_string, illegal_chars)

import com.crystalprism.ce.formula.HybridFormulaEvaluator;
import com.crystalprism.ce.usermodel.*;

public class FormulaIteratorExample5 {
    public static void main(String[] args) {
        double initValue = 0, increment = 1;
        FormulaIterator formulaIterator = new FormulaIterator(initValue, increment, LoopOperator.VOID);
        String textToClean = "1ONE1 2TWO2 3THREE3";
        String myLambdaText =
                "IFS(NEXT()=0, text_string, " +
                    "NEXT()<=LEN(illegal_chars), SUBSTITUTE(VALUE(), MID(illegal_chars, NEXT(), 1), \"\"), " +
                    "NEXT()>LEN(illegal_chars), STOP())";
        String myFormulaText = "REPLACECHARS(text_string, illegal_chars)";
        LambdaField myLambdaField = new LambdaField(ComputeFieldType.ALPHANUMERIC, myLambdaText);
        FormulaField myFormulaField = new FormulaField(ComputeFieldType.ALPHANUMERIC, myFormulaText);
        CalculationContext calculationContext = new CalculationContext("FormulaIteratorExample5");

        calculationContext.put("text_string", new InputField(ComputeFieldType.ALPHANUMERIC, textToClean));
        calculationContext.put("illegal_chars", new InputField(ComputeFieldType.ALPHANUMERIC, "123"));
        calculationContext.put("REPLACECHARS", myLambdaField);
        calculationContext.put("my_formula", myFormulaField);

        myFormulaField.setFormulaIterator(formulaIterator);
        HybridFormulaEvaluator.evaluateFormula("my_formula", calculationContext);
        System.out.println("Calculated result: " + myFormulaField.getDisplayValue());
    }
}

Calculated result: ONE TWO THREE