LAMBDA Function

The Adaptive Calculation Engine implementation of the Excel LAMBDA function

Introduction

The ComputeField interface is a high level representation of the Adaptive Calculation Engine InputFields, FormulaFields, and LambdaFields.

The InputFields and FormulaFields are analogous to the Excel input and formula cells while the new LambdaFields are analogous to the Excel LAMBDA functions. They are used to create custom reusable functions for the Adaptive Calculation Engine formulas.

Illustration of the Excel LAMBDA function via LAMBDA field.

The below code creates three ComputeFields: inputField1, inputField2, and formulaField analogous to input cells B2 and C2, and formula cell A2. It stores them in the ComputeFileds HashMap with corresponding key names. Finally it invokes the static method HybridFormulaEvaluator.evaluateFormula to evaluate the formulaField.

ComputeField inputField1 = new InputField(ComputeFieldType.NUMERIC, 2);
ComputeField inputField2 = new InputField(ComputeFieldType.NUMERIC, 3);
ComputeField formulaField = new FormulaField(ComputeFieldType.NUMERIC, "B2 + C2");
CalculationContext calculationContext = new CalculationContext("LambdaFieldIntro");

calculationContext.put("B2", inputField1);
calculationContext.put("C2", inputField2);
calculationContext.put("A2", formulaField);
HybridFormulaEvaluator.evaluateFormula("A2", calculationContext);

LAMBDA Field

The below code creates the LAMBDA field lambdaField based on the formula field in this example:

ComputeField lambdaField = new LambdaField("X + Y");

The lambdaField was created from the formula X + Y.
Its LAMBDA function declaration can be obtained from from the getLambdaFunction() method:

System.out.println(lambdaField.getValue()); 

LAMBDA(X, Y, X + Y)

This LAMBDA function declaration can be described via the following pseudo code:

function LAMBDA(X, Y) {
    return X + Y;
}

The code below shows how the FormulaField A2 in this example can use a custom function ADD2NUMBERS built on this LAMBDA function:

computeFields.put("ADD2NUMBERS", lambdaField);
ComputeField formulaField = new FormulaField(ComputeFieldType.NUMERIC, "ADD2NUMBERS(B2, C2)";
computeFields.put("A2", formulaField);
HybridFormulaEvaluator.evaluateFormula("A2", computeFields);

Syntax

The Adaptive Calculation Engine Lambda function has the same syntax as the Microsoft LAMBDA function:

LAMBDA([parameter1, parameter2, ...,] calculation)

parameterN

A value that will be passed as an argument to the function, such as a ComputeField name, a ComputeFields range name, a TableArray name, a constant, or any suitable function. This argument is optional.

calculation

The formula you want to execute and return as the result of the function. This argument is required.

Example 1

In this example the lambdaField is created based on the VLOOKUP function that looks up fruits in the second column of the TableArray lookup_table based on the available fruit amounts. Its LAMBDA function has one parameter lookup_value and is named MYLOOKUP. It is used by the following formula my_formula:

my_formula=MYLOOKUP(20)

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

public class LambdaFieldExample1 {
    public static void main(String[] args) {
        Object[][] tableArrayBase = {{10, "Apple"},
                                     {20, "Pear"},
                                     {30, "Orange"}};
        TableArray tableArray = new TableArray(tableArrayBase);
        String lambdaFormulaText = "VLOOKUP(lookup_value, \"lookup_table\", 2, 0)";
        LambdaField lambdaField = new LambdaField(ComputeFieldType.ALPHANUMERIC, lambdaFormulaText);
        CalculationContext calculationContext = new CalculationContext("LambdaFieldExample1");

        // ComputeFields
        calculationContext.put("MYLOOKUP", lambdaField);
        calculationContext.put("my_formula", new FormulaField(ComputeFieldType.ALPHANUMERIC, "MYLOOKUP(20)"));
        // TableArray
        calculationContext.put("lookup_table", tableArray);

        HybridFormulaEvaluator.evaluateFormula("my_formula", calculationContext);
        System.out.println("Lambda function: " + lambdaField.getLambdaFunction());
        System.out.println("Calculated result: " + calculationContext.get("my_formula").getDisplayValue());
    }
}

Lambda function: LAMBDA(lookup_value, VLOOKUP(lookup_value, "lookup_table", 2, 0))
Calculated result: Pear

Example 2

In this example the lambdaField is created based on the COUNTIFS function that counts numbers that are greater than 60. Its LAMBDA function has one ComputeFields range parameter range_parameter and is named MYCOUNT. It is used by the following formula my_formula:

my_formula=MYCOUNT(criteria_range)

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

public class LambdaFieldExample2 {
    private static ComputeField createInputField(Object value) {
        ComputeField computeField = new InputField(ComputeFieldType.NUMERIC, value);
        computeField.setRangeName("criteria_range");
        return (computeField);
    }

    public static void main(String[] args) {
        String lambdaFormulaText = "COUNTIFS(range_parameter, \">\", 60)";
        LambdaField lambdaField = new LambdaField(ComputeFieldType.NUMERIC, lambdaFormulaText);
        CalculationContext calculationContext = new CalculationContext("LambdaFieldExample2");

        calculationContext.put("criteria_val1", createInputField(59));
        calculationContext.put("criteria_val2", createInputField(64));
        calculationContext.put("criteria_val3", createInputField(85));
        calculationContext.put("criteria_val4", createInputField(95));

        calculationContext.put("MYCOUNT", lambdaField);
        calculationContext.put("my_formula", new FormulaField(ComputeFieldType.NUMERIC, "MYCOUNT(criteria_range)"));
        HybridFormulaEvaluator.evaluateFormula("my_formula", calculationContext);
        System.out.println("Lambda function: " + lambdaField.getLambdaFunction());
        System.out.println("Calculated result: " +  calculationContext.get("my_formula").getDisplayValue());
    }
}

Lambda function: LAMBDA(range_parameter, COUNTIFS(range_parameter, ">", 60))
Calculated result: 3

Example 3

In this example the lambdaField is created based on the user defined function FTOC that converts the temperature in degrees Fahrenheit to degrees Celsius. Its LAMBDA function has one parameter X and is named TOCELCIUS. It is used by the following formula my_formula:

my_formula=TOCELCIUS(100)

import com.crystalprism.ce.exception.UDFException;
import com.crystalprism.ce.formula.HybridFormulaEvaluator;
import com.crystalprism.ce.formula.UDFLocator;
import com.crystalprism.ce.usermodel.*;
import com.crystalprism.ce.examples.expression.userdefinefunction.*;

public class LambdaFieldExample3 {
    public static void main(String[] args)
    {
        try {
            String myUDFClassName = UDFLocator.add(UserFunctionsImpl.class, UserFunctionsInterface.class);
            String lambdaFormulaText = "ROUND(FTOC(X),0)";
            LambdaField lambdaField = new LambdaField(ComputeFieldType.NUMERIC, lambdaFormulaText, myUDFClassName);
            CalculationContext calculationContext = new CalculationContext("LambdaFieldExample3");

            calculationContext.setUDFClassName(myUDFClassName);
            calculationContext.put("TOCELCIUS", lambdaField);
            calculationContext.put("my_formula", new FormulaField(ComputeFieldType.NUMERIC, "TOCELCIUS(100)"));
            HybridFormulaEvaluator.evaluateFormula("my_formula", calculationContext);
            System.out.println("Lambda function: " + lambdaField.getLambdaFunction());
            System.out.println("Calculated result: " + calculationContext.get("my_formula").getDisplayValue());
        }
        catch (UDFException e) {
            System.out.println(e.getMessage());
        }
    }
}

Lambda function: LAMBDA(X, ROUND(FTOC(X),0))
Calculated result: 38

Example 4

In this example the recursive Lambda function recur_adder() is built based on the example published in the following article. It takes four arguments: iteration - the current iteration of the function; times_to_add - the number of times to add the previous numbe; value - the initial (current) value; value_to_add - the value to add. The recur_adder() function adds the value_to_add to the value for as long as the iteration is less than the times_to_add. To achieve this it uses the second Lambda function adder(), which takes two arguments, the value and the value_to_add. The following iterative formula my_formula performs 1,000,000 recursive iterative invocations of the recur_adder() function in about 10 seconds without experiencing any limit of iterations:

my_formula=recur_adder(iteration, times_to_add, value, value_to_add)

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

public class LambdaFieldExample4 {
    public static void main(String[] args) {
        String lambdaFormulaText1 = "value + value_to_add";
        String lambdaFormulaText2 = "IF(iteration<times_to_add, " +
                "recur_adder(iteration+1, times_to_add, adder(value, value_to_add), value_to_add), value)";
        String formulaTex = "recur_adder(iteration, times_to_add, value, value_to_add)";

        LambdaField lambdaField1 = new LambdaField(ComputeFieldType.NUMERIC, lambdaFormulaText1);
        LambdaField lambdaField2 = new LambdaField(ComputeFieldType.NUMERIC, lambdaFormulaText2);
        CalculationContext calculationContext = new CalculationContext("LambdaFieldExample4");

        calculationContext.put("value", new InputField(ComputeFieldType.NUMERIC, 100));
        calculationContext.put("value_to_add", new InputField(ComputeFieldType.NUMERIC, 1));
        calculationContext.put("times_to_add", new InputField(ComputeFieldType.NUMERIC, 1000000));
        calculationContext.put("iteration", new InputField(ComputeFieldType.NUMERIC, 0));

        calculationContext.put("adder", lambdaField1);
        calculationContext.put("recur_adder", lambdaField2);
        calculationContext.put("my_formula", new FormulaField(ComputeFieldType.NUMERIC, formulaTex));
        HybridFormulaEvaluator.evaluateFormula("my_formula", calculationContext);

        System.out.println("adder()       = " + lambdaField1.getLambdaFunction());
        System.out.println("recur_adder() = " + lambdaField2.getLambdaFunction().substring(0, 52));
        System.out.println("                " + lambdaField2.getLambdaFunction().substring(53, 79));
        System.out.println("                " + lambdaField2.getLambdaFunction().substring(80, 161));
        System.out.println("                " + lambdaField2.getLambdaFunction().substring(162));
        System.out.println("Calculated result: " + calculationContext.get("my_formula").getDisplayValue());
    }
}

adder()       = LAMBDA(value, value_to_add, value + value_to_add)
recur_adder() = LAMBDA(iteration, times_to_add, value, value_to_add,
                IF(iteration<times_to_add,
                recur_adder(iteration+1, times_to_add, adder(value, value_to_add), value_to_add),
                value))
Calculated result: 1000100

Example 5

In this example the recursive Lambda function REPLACECHARS() is built based on the example published in the following article. It iterates over the list of "illegal" characters (in our example '1', '2', and '3') to be removed. The IF statement says if there are no more "illegal" characters, return the input text_string, and otherwise remove each occurrence of the leftmost character in illegal_chars. Recursion kicks in with the request to call REPLACECHARS() again with the updated string, and the rest of illegal_chars as it shown in the following formula my_formula:

my_formula=REPLACECHARS(illegal_chars, text_string)

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

public class LambdaFieldExample5 {
    public static void main(String[] args) {
        String myLambdaText = "IF(illegal_chars=\"\", text_string, " +
                                "REPLACECHARS(RIGHT(illegal_chars, LEN(illegal_chars)-1), " +
                                                "SUBSTITUTE(text_string, LEFT(illegal_chars, 1), \"\")))";
        String myFormulaText = "REPLACECHARS(illegal_chars, text_string)";
        String textToClean = "1ONE1 2TWO2 3TREE3";

        LambdaField myLambdaField = new LambdaField(ComputeFieldType.ALPHANUMERIC, myLambdaText);
        FormulaField myFormulaField = new FormulaField(ComputeFieldType.ALPHANUMERIC, myFormulaText);
        String lambdaFunction = myLambdaField.getLambdaFunction();
        CalculationContext calculationContext = new CalculationContext("LambdaFieldExample5");

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

        System.out.println("REPLACECHARS() = " +  lambdaFunction.substring(0, 34));
        System.out.println("                   " +  lambdaFunction.substring(35, 68));
        System.out.println("                      " +  lambdaFunction.substring(69, 125));
        System.out.println("                                   " +  lambdaFunction.substring(126, 173));
        System.out.println("                      " +  lambdaFunction.substring(174));
        System.out.println("\nText without illegal chars : " + myFormulaField.getDisplayValue());
    }
}

REPLACECHARS() = LAMBDA(illegal_chars, text_string,
                    IF(illegal_chars="", text_string,
                        REPLACECHARS(RIGHT(illegal_chars, LEN(illegal_chars)-1),
                                    SUBSTITUTE(text_string, LEFT(illegal_chars, 1),
                        ""))))

Text without illegal chars : ONE TWO THREE

Example 6

In this example the recursive Lambda function Fibonacci() calculates the Fibonacci numbers as shown in the following formula my_formula:

my_formula=Fibonacci(X)

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

public class LambdaFieldExample6 {
    public static void main(String[] args) {
        String lambdaFormulaText = "IF(X < 2, X, Fibonacci(X - 1) + Fibonacci(X - 2))";
        String formulaTex = "Fibonacci(X)";

        LambdaField lambdaField = new LambdaField(ComputeFieldType.NUMERIC, lambdaFormulaText);
        CalculationContext calculationContext = new CalculationContext("FibonacciFunction");

        calculationContext.put("X", new InputField(ComputeFieldType.NUMERIC, 20));
        calculationContext.put("Fibonacci", lambdaField);
        calculationContext.put("my_formula", new FormulaField(ComputeFieldType.NUMERIC, formulaTex));
        HybridFormulaEvaluator.evaluateFormula("my_formula", calculationContext);

        System.out.println("Fibonacci() = " + lambdaField.getLambdaFunction());
        System.out.println("Calculated result: " + calculationContext.get("my_formula").getDisplayValue());
    }
}

Fibonacci() = LAMBDA(X, IF(X < 2, X, Fibonacci(X - 1) + Fibonacci(X - 2)))
Calculated result: 6765