F(x) Expressions

What is F(x)?

F(x) is a powerful expression evaluator integrated within Perigee, designed to enhance data manipulation and computation capabilities. Much like the "Formula" bar in Excel, F(x) allows users to input complex formulas incorporating functions, mathematical operations, column references, and parentheses. However, F(x) goes beyond basic evaluation by offering advanced features:

• Custom Functionality: Users can add custom classes with functions, expanding the evaluator's capabilities to suit specific needs.

• Performance Optimization: Instead of repeatedly tokenizing, parsing, and evaluating expressions, F(x) compiles expressions into Intermediate Language (IL) code. This compilation results in a direct expression tree that can be executed swiftly, making it ideal for processing large datasets efficiently.

How can I use F(x)?

Possibilities are endless, but let's examine a few cool ideas.

• Enable an input box to accept math expressions allowing the user to easily double a value, or figure out what a third of their original input was.

• Execute an expression across an entire DataTable, modifying the values of each row. This allows for easy data manipulation and cross references to other columns.

• Enabling users to create dynamic data output for a report.

• Allow the user to write their own filter on data, tables, report output, or data ingestion.

Examples

Example 1) Math expression with identifiers

In this example, we'll see how to create an Fx class, register functions, compile a formula, and get results. We can see what the identifier callback is and how to use it.

Notice we return FxValue? This special class handles all of the data types and conversions for the evaluator. If you're passing data in or out of Fx, it will be in the form of a FxValue.

PerigeeApplication.App("Fx", (c) => {

    //Declare a new Fx class, and register the default functions shipped alongside it.
    Fx fx = new Fx();
    fx.RegisterRoot<FxDefaultFunctions>();

    //Compile a new expression
    var fnMethod = fx.Compile("abs([a]) * 2.5");

    //Get the results
    FxValue fnResults = fnMethod(
        //the idCallback will be called when an identifier [value] is found and needs to be referenced. 
        (idCallback) =>
        {

            if (idCallback.Identifier == "a")
                return FxValue.From(10m);
            else
                return FxValue.From(1m);
        });

    //prints 25.0
    c.GetLogger<Program>().LogInformation("Result: {v}", fnResults);

    c.ExitApplication();
});

Example 2) Method overrides

Just like last time we'll be compiling an expression, but this time we'll add the method override. This special override allows you inject your own method handing. In this case, we'll inject a "custom" method that we divide the value by 100. If the requested method isn't "custom", then we allow fx to call the methods as it usually would with fx.CallMethod(fn, arg).

PerigeeApplication.App("Fx", (c) => {

    //Declare a new Fx class, and register the default functions shipped alongside it.
    Fx fx = new Fx();
    fx.RegisterRoot<FxDefaultFunctions>();

    //Compile a new expression, with a method override
    var fnMethod = fx.Compile("custom([a]) * 2.5", (arg, fn, token, _) => { 
        if (fn.Equals("custom") && arg.Length > 0)
        {
            return FxValue.From(arg[0].AsDecimal() / 100.0m);
        }
        return fx.CallMethod(fn, arg);
    });

    //Get the results
    FxValue fnResults = fnMethod(
        //the idCallback will be called when an identifier [value] is found and needs to be referenced. 
        (idCallback) =>
        {
            if (idCallback.Identifier == "a")
                return FxValue.From(10m);
            else
                return FxValue.From(1m);
        });

    //prints 0.25
    c.GetLogger<Program>().LogInformation("Result: {v}", fnResults);

    c.ExitApplication();
});

Example 3) Input parameters

Sometimes you need input context when processing methods, the CompileWithInputParameter allows you to do exactly this, pass in anything that conforms to the object type (including a class) and it will be available for you in the callback. This is especially useful when processing lists, tables, or modifying behavior based on the index, row, user, etc.

This time we'll be returning a string value, and prepending "name: " to it. The passed in input parameter allows us to reference our lookup dictionary from within the method override call.

PerigeeApplication.App("Fx", (c) => {

    //Declare a new Fx class, and register the default functions shipped alongside it.
    Fx fx = new Fx();
    fx.RegisterRoot<FxDefaultFunctions>();

    Dictionary<string, string> NameReferences = new Dictionary<string, string>() { { "0", "bob" }, { "1", "jane" }, { "2", "john" }, { "3", "ruth" } };

    //Compile a new expression, with a method override
    var fnMethod = fx.CompileWithInputParameter<string>("'name: ' & custom()", (arg, fn, token, inp) => { 
        if (fn.Equals("custom"))
        {
            return FxValue.From(NameReferences.GetValueOrDefault(inp, ""));
        }
        return fx.CallMethod(fn, arg);
    });

    //Get the results, this time passing in "0" as the input parameter
    FxValue fnResults = fnMethod("0",
        //the idCallback will be called when an identifier [value] is found and needs to be referenced. 
        (idCallback) =>
        {
            
            if (idCallback.Identifier == "a")
                return FxValue.From(10m);
            else
                return FxValue.From(1m);
        });

    //prints "name: bob"
    c.GetLogger<Program>().LogInformation("Result: {v}", fnResults);

    c.ExitApplication();
});

Example 4) Custom functions

This time we'll add a CustomFunctions class to our project, and allow Fx to call it. We added a square root method called "root". Running this example will call the method and return the value (2.449 ...)

PerigeeApplication.App("Fx", (c) => {

    //Declare a new Fx class, and register the default functions shipped alongside it.
    Fx fx = new Fx();
    fx.RegisterRoot<FxDefaultFunctions>();
    fx.RegisterRoot<CustomFunctions>();

    //Compile a new expression, with a method override
    var fnMethod = fx.Compile("root(6)");

    //Get the results
    FxValue fnResults = fnMethod(
        (idCallback) => FxValue.From(0m));

    //prints  2.4494897
    c.GetLogger<Program>().LogInformation("Result: {v}", fnResults);

    c.ExitApplication();
});

public class CustomFunctions
{
    public static FxValue root(FxValue dbl) => FxValue.From(Math.Sqrt(dbl.AsDouble()));
}

Example 5) Table evaluation

In this example, we'll run over a data table with a 1000 rows. We can supply each columns expression allowing us to perform various operations on each column, sequentially.

We'll also use a partitioned sum to set the Calc column (identical to a SUM(Amount) / group by Type).

Then finally, override the Type column with a concatenation of the Org and Type.

//Generate 1K rows
DataTable DT_Sample = new DataTable();
DT_Sample.Columns.Add("Org", typeof(int));
DT_Sample.Columns.Add("Type", typeof(string));
DT_Sample.Columns.Add("Amount", typeof(decimal));
DT_Sample.Columns.Add("Calc", typeof(decimal));
_FillTable(DT_Sample, 1000);

PerigeeApplication.App("Fx", (c) => {

    //Declare a new Fx class, and register the default functions shipped alongside it.
    Fx fx = new Fx();
    fx.RegisterRoot<FxDefaultFunctions>();

    //Run over the data and set the Calc field to a sum partition, and then reassign Type to a concatenation
    fx.CompileTable(DT_Sample, new Dictionary<string, string>() {
        { "Calc", "SUM([Amount] PARTITION BY [Type])" },
        { "Type", "[Org] & '-' & [Type]"}
    });

    c.GetLogger<Program>().LogInformation("Result 0: {@v}", DT_Sample.Rows[0].ItemArray.ToList());

    c.ExitApplication();
});

void _FillTable(DataTable table, int count)
{
    string[] types = { "A", "B", "C", "D", "E" };

    Random rand = new Random();

    table.BeginLoadData();

    try
    {
        for (int i = 0; i < count; i++)
        {
            int org = rand.Next(1, 1001); // Random integer between 1 and 1000
            string type = types[rand.Next(types.Length)]; // Random type from the array
            decimal amount = Math.Round((decimal)(rand.NextDouble() * 10_000), 2); // Random decimal between 0.00 and 10,000.00

            table.Rows.Add(org, type, amount, 0.0m);
        }
    }
    finally
    {
        table.EndLoadData();
    }
}

Valid types

• Booleans values are supplied as: true, false, yes, no

  • When coercing a String or Number value into a boolean, a 0, 1 will also be accepted

• Numbers are supplied as whole or with a decimal point: 5, 2.5

• Strings are within double quotes or single quotes: "name", 'day'

• Date values are supplied in a string: '01-01-1900'

• Timespan values are supplied variably:

  • 01:10 is 1 hour, 10 minutes

  • 01:10:22 is 1 hour, 10 minutes, 22 seconds

  • 01:10:22:55 is 1 day 10 hours, 22 minutes, 55 seconds

  • 01:10:22:55.110 is is 1 day 10 hours, 22 minutes, 55 seconds, 110 milliseconds

Valid Operators

• + for addition

• - for subtraction

• * For multiplication

• / for division

• ^ for power

• & for concatenation

• % for modulus

• == or = for equals

• != for not equals

• All < > >= <= comparison symbols for greater/lesser than.

Default Functions

All of the functions listed below are included in the FxDefaultFunctions class.

Format / Multi-use

Format (Format)

Formats a date, time span, or decimal according to the specified format. Culture is optional and follows the CultureInfo defined by Microsoft.

 codeFx.Compile("Format(fmtValue, format, cultureInfo = null)");

In the below examle, this would format a string value containing a decimal in France currency with 2 decimal places.

format('12345.234', 'C2', 'fr-FR')
// 12 345,23 €

Number Functions

Absolute Value (abs)

Ensures the output value is always positive.

codeFx.Compile("abs(1)");

Maximum (max)

Returns the larger of two numbers.

codeFx.Compile("max(5, 10)");

Minimum (min)

Returns the smaller of two numbers.

codeFx.Compile("min(5, 10)");

Sine (sin)

Calculates the sine of a given angle (in radians).

codeFx.Compile("sin(0)");

Cosine (cos)

Calculates the cosine of a given angle (in radians).

codeFx.Compile("cos(0)");

Tangent (tan)

Calculates the tangent of a given angle (in radians).

codeFx.Compile("tan(0)");

Log Base 10 (log10)

Calculates the base-10 logarithm of a number.

codeFx.Compile("log10(100)");

Square Root (sqrt)

Calculates the square root of a number.

codeFx.Compile("sqrt(16)");

Round (round)

Rounds a number to a specified number of decimal places.

codeFx.Compile("round(3.14159, 2)");

Places (places)

Rounds a number to a specified number of decimal places without exceeding the original value.

codeFx.Compile("places(3.145, 2)");

Ceiling (ceil)

Rounds a number up to the nearest integer.

codeFx.Compile("ceil(3.14)");

Floor (floor)

Rounds a number down to the nearest integer.

codeFx.Compile("floor(3.14)");

---

Null Handling

Null If (NullIf)

Returns null if the first value equals the fallback value; otherwise, returns the first value.

codeFx.Compile("NullIf(value, fallback)");

Is Null (IsNull)

Returns the fallback value if the first value is null; otherwise, returns the first value.

codeFx.Compile("IsNull(value, fallback)");

---

Conditionals

If (If)

Returns ifTrue if the condition is true; otherwise, returns ifFalse.

codeFx.Compile("If(condition, ifTrue, ifFalse)");

Coalesce (Coalesce)

Returns the first non-null value from the provided conditions.

codeFx.Compile("Coalesce(value1, value2, value3)");

---

Logical Operations

And (and)

Returns true if all conditions are true; otherwise, returns false.

codeFx.Compile("and(condition1, condition2)");

Or (or)

Returns true if any condition is true; otherwise, returns false.

codeFx.Compile("or(condition1, condition2)");

Not (not)

Inverts the boolean value of the condition.

codeFx.Compile("not(condition)");

If Error (iferror)

Returns the fallback value if the first value is an error; otherwise, returns the first value.

codeFx.Compile("iferror(value, fallback)");

Is Error (iserror)

Checks if the value is an error.

codeFx.Compile("iserror(value)");

Is Number (isnumber)

Checks if the value is a number.

codeFx.Compile("isnumber(value)");

Is String (isstr)

Checks if the value is a string.

codeFx.Compile("isstr(value)");

Is Blank (isblank)

Checks if the value is null or an empty string.

codeFx.Compile("isblank(value)");

Is Boolean (isbool)

Checks if the value is a boolean.

codeFx.Compile("isbool(value)");

---

Financial Functions

Payment (pmt)

Calculates the payment for a loan based on constant payments and a constant interest rate.

codeFx.Compile("pmt(rate, nper, pv, fv)");

Principal Payment (ppmt)

Calculates the principal part of a payment for a specific period.

 codeFx.Compile("ppmt(rate, per, nper, pv, fv, type)");

Interest Payment (ipmt)

Calculates the interest part of a payment for a specific period.

codeFx.Compile("ipmt(rate, per, nper, pv, fv, type)");

---

Date Functions

Today (today)

Returns the current date.

codeFx.Compile("today()");

Now (now)

Returns the current date and time.

codeFx.Compile("now()");

Date (date)

Creates a date from year, month, and day.

codeFx.Compile("date(year, month, day)");

Date Add (DateAdd)

Adds a specified number of intervals to a date.

codeFx.Compile("DateAdd(datePart, number, date)");

Date Difference (DateDiff)

Calculates the difference between two dates based on the specified date part.

codeFx.Compile("DateDiff(datePart, date1, date2)");

---

String Functions

Contains (contains)

Returns a boolean of whether or not the string contains a value

codeFx.Compile("contains(stringToSearch, valueToFind, caseSensitive = false)");

Name Parts (name)

Parse a name and return a name part. Useful when a column contains a full name and you need to split it.

Valid name parts:

• title

• first

• last

• middle

• nickname (nickname)

• suffix

• fullname (first middle last)

• all (all name parts, just cleaned up and optionally title cased)

codeFx.Compile("name(nameValue, namePart, TitleCaseResult = false)");

Name Format (nameformat)

Just like the name function, it allows you the freedom to format the name result in any way you like. Example: {first}/{last} -> Bob/Jones

Valid name replacement strings:

• {title}

• {first}

• {last

• {middle

• {nickname} (nickname)

• {suffix}

codeFx.Compile("nameformat(nameValue, format, TitleCaseResult = false)");

Split and Take (rejoin) (split_take)

Split take works a lot like split, but you can specify a range of results to rejoin.

codeFx.Compile("split_take(value, range, join, separators[])");

An example of this is:

split_take('a,b,c,d,e,f,g', '0,1,5-20', '-', ',')

This would return a-b-f-g. as it takes the first two elements, then elements 5 through 20 if they exist.

Split (split)

Split a string and return a split value

codeFx.Compile("split(value, index, defaultValue, separators[])");

The index is zero based, meaning 0 is the first item. You can use 'first', or 'last' as well to retrieve those values.

defaultValue can be supplied when an index is out of range, or the value is null.

You may supply as many string separators as you need, such as:

split([Notes],'last', 'N/A', ',', '|', '@')

Repeat (repeat)

Repeats a sequence of strings a specified number of times.

codeFx.Compile("repeat(count, seq)");

Concatenate (concat)

Concatenates multiple strings into one.

codeFx.Compile("concat(value1, value2, value3)");

Substring (substr)

Extracts a substring from a string starting at a specified index with a specified length.

codeFx.Compile("substr(text, start, length)");

Left (left)

Returns the leftmost specified number of characters from a string.

codeFx.Compile("left(value, len)");

Right (right)

Returns the rightmost specified number of characters from a string.

 codeFx.Compile("right(value, len)");

Trim (trim)

Removes leading and trailing whitespace from a string.

codeFx.Compile("trim(value)");

Trim with Characters (trim)

Removes specified characters from the start and end of a string.

codeFx.Compile("trim(value, chars)");

Replace (replace)

Replaces all occurrences of a specified substring with another substring.

codeFx.Compile("replace(value, search, replacement)");

Upper (upper)

Converts a string to uppercase.

codeFx.Compile("upper(value)");

Lower (lower)

Converts a string to lowercase.

codeFx.Compile("lower(value)");

Length (len)

Returns the length of a string.

codeFx.Compile("len(value)");

---

Aggregate Functions

Sum Values (sumvals)

Calculates the sum of multiple numerical values.

codeFx.Compile("sumvals(value1, value2, value3)");

Average Values (avgvals)

Calculates the average of multiple numerical values.

codeFx.Compile("avgvals(value1, value2, value3)");