Summary

This is a legacy version of Formula operator (row-by-row) (5.0).

A user-defined formula can be entered in C# syntax. This formula is then applied to each row of the input table sequentially starting at the first row. A new column is created containing the results of each row's calculation. As the operator processes the rows in a sequentially, it can refer to the calculation results of the previous rows.

Examples: Calculating a sum of values from two columns, re-formatting data, writing "if ... then ... else ..." scripts, creating "signal lights", etc.

  • Want to learn more?


    • Formula operator

    Date and Time

    Text

    General Mathematics

    Conversion and Formatting

    Extras

    XIH Helper Functions

    Configuration

    Settings

    Name

    Value

    Opt.

    Description

    Example

    Function

    System.String

    -

    User-defined function in C# syntax. Select "Edit..." to modify the formula and add parameters.

    -

    Name of the result column

    System.String

    -

    Name of the result column.

    -

    Result column type

    System.String

    • Text
    • Integer
    • Number with decimal places
    • Yes/No value
    • Date

    -

    Data Type of the result column.

    -

    Delete result column after calculation

    System.Boolean

    -

    Operator creates an additional column for calculation results. This column may not be necessary, e.g., if the operator only writes values in existing columns using XIH-functionality.

    -

    Want to learn more?

    Using C# in TIS

    C# Syntax:

    • Formulas and more information under MSDN
    • C# is case sensitive. Be careful with upper and lower case letters!
    •  Write the constant with decimal places --> 7.0 if an operation is supposed to yield a number with decimal places and you are working with constants.
    • class names must be fully qualified, e.g. System.DataTime.Now

    Specific in TIS:

    • Variables have to be enclosed by # to be recognized. #Length#+#Width# means that the values of the columns "Length" are added to the values of column "Width".
    • Additional functions, such as the Ximes Helper (XIH) Functions and built-in variables such as rownumber provide additional functionality. Please refer to the sections below.
    • XIH Functions do NOT allow # in the notation of variables. Example: write "Identifier" instead of "#Identifier#".

    Variables can be

    • columns
    • TIS parameters
    • manual entries

    NOTE: manual entries can only be named at creation time. Click into the combo box with text "Manual Entry..." and edit the text.

    NOTE: manual entries must not contain spaces in their names.

    Good editors for formula operator: Easier editing in the Formula Operator

    Examples and Formulas

    Example 1: Add values from two columns

    Situation

    Step 1

    Edit function in the Operator settings

    Step 2

     Enter formula and click "Apply"

    Please note: Variables have to be enclosed by # to be recognized.

    #Length#+#Width# means, that the values of the columns "Length" are added to the values of column "Width".

    Result

    TIS Project

    Formula operator (row-by-row) (4.0) = Formeloperator

    Example 2: Multiplying two values

    Situation

    Operation setting

    Please note: Variables have to be enclosed by # to be recognized. #Length#*#Width# means, that the values of the column "Length" are multiplied by the values of column "Width".

    Result


    Example 3: Calculation with integral numbers or decimals

    • 5 / 2 equals 2 (integral number, rounded down). Without conversion to Double, the C# Compiler automatically converts to integral numbers.
    • 5.0 / 2.0 equals 2.5 (decimal number).
    • 5 / 2.0 equals 2.5 (decimal number), because at least 1 number is a decimal.
    • (double) #Integral number column# converts an integral number to a decimal.

    Example 4: If then else

    Description

    Formula

    If value in the column is greater than 3, then write 1, else write 0 into result column.

    #Column# > 3.0 ? 1.0 : 0.0

    If the date of the column lies between 2003/01/01 and 2003/09/30, then write 1, else write 0 into result column.

    #Date#.Year == 2003 && #Date#.Month <= 9 ? 1 : 0

    Example of a complex If:

    #timefrom# >1000.0 ? "N" : (#timefrom# < 100.0 ? "Free" : "F")


    Example 5: Write set values into a column

    Description

    Formula

    Write the same text into each row of a column: The text must always be written between inverted commas. Additionally, the result column type must be set to Text.

    "Same text"


    Example 6: Two columns shall be merged to one identifier

    Description

    Formula

    Extends the functionality of Combine columns, e.g., yields a text separated by_  

    (Example: 01.01.2007 00:00:00_Department1)

    #dateColumn#.ToString() + "_" + #textColumn#


    Syntax

    Symbol

    Description

    ==

    equals

    %

    modulo

    ||

    logical or

    &&

    logical and


    Snippets

    Key word

    Background

    Requirements

    Code

    Effect

    Avoid empty rows

    It is often practical to avoid empty rows, e.g. because calculations based on these rows would not function with empty rows, or users would wonder why nothing appears.

    • In column “Keep” it is coded with 1 or 0 if the row should be kept or not.
    • This column needs to be named “Helper” for the new formula
    • The columns “Value” and “Description” need to be renamed accordingly if values in these columns should be deleted.
    (XIH.EqualsPrev("ID") == false ? #Keep# :
    (XIH.EqualsNext("ID") ?
     (XIH.GetInt("Helper", -1) + #Keep#)
     : ( XIH.GetInt("Helper", -1) == 0 && #Keep# == 0?
          (XIH.SetInt("Keep", 1) &&
          XIH.SetDouble("Value", 0) &&
          XIH.SetString("Description", "no matching data found")
             ? 0:-999) : 0)))

    Or without ID

    (rowNumber < (rows - 1) ?
    (XIH.GetInt("Helper", -1) + #Keep#)
    : ( XIH.GetInt("Helper", -1) == 0 && #Keep# == 0?
         (XIH.SetInt("Keep", 1) &&
         XIH.SetDouble("Value", 0) &&
         XIH.SetString("Description", "no matching data found")
            ? 0:-999) : 0))

    The last sentence remains. Values are being deleted.

    Sort weekdays


    See also above "Date and Time"

    If weekdays are displayed as Mo, Di … they will be sorted incorrectly.

    • German names with at least two letters
    • Variable #WoTag# 
    0
    + (#WoTag#.Contains("Mo") ? 1 : 0)
    + (#WoTag#.Contains("Di") ? 2 : 0)
    + (#WoTag#.Contains("Mi") ? 3 : 0)
    + (#WoTag#.Contains("Do") ? 4 : 0)
    + (#WoTag#.Contains("Fr") ? 5 : 0)
    + (#WoTag#.Contains("Sa") ? 6 : 0)
    + (#WoTag#.Contains("So") ? 7 : 0)

    The result is an Integer which can be sorted.

    Create HTML tables

    With nice formatting.

    Example: Adapt columns and formatting

    Structure in formula operator: column HTML – data type Text

    /* Fix Prefix*/
    (rowNumber == 0 ?
    ( "<tableX border='1'" +
    " bordercolor='#E8E8E8' cellpadding='5' cellspacing='0' width='100%'>" +
    /*adapt column number*/
    "<colgroupX> <col width='30%' align='left'>  <col width='20%' align='right'>" +
    "<col width='20%' align='left'> <col width='30%' align='left'>  </colgroupX>" +
    "<thead bgcolor='#C0C0C0'><tr>" +
    /*Adapt column headings */
    "<th>Name input value</th><th align='right'>Wert</th><th>Unit</th><th>Comments</th></tr></thead>") : "") +
    "<trX bgcolor='#FFFFFF'>" +
    /* ROW STRUCTURE */
    "<trX>" +
    "<tdX>" + #Input# + "" +  "</tdX>" +
    "<tdX align='right'>" + #Value# + "" +  "</tdX>" +
    "<tdX>" + #Unit# + "" +  "</tdX>" +
    "<tdX>" + (XIH.IsNull("Comment") || #Comment# =="" ? "-" : #Comment#) +
    "" + "</tdX>" +
     /* ROW ENDING */
    (rowNumber == rows-1 ? ("</tableX>") : "")

    Afterwards concatenate the single rows to one cell with Spalteninhalt verketten.
    In the end replace the Steuerungszeichen with 2nd formula operator (result type Boolean)

    XIH.SetString("HTML", #HTML#.Replace("tableX", "table")) &&
    XIH.SetString("HTML", #HTML#.Replace("trX", "tr")) &&
    XIH.SetString("HTML", #HTML#.Replace("tdX", "td"))&&
    XIH.SetString("HTML", #HTML#.Replace("colgroupX", "colgroup"))

    and delete unnecessary columns except column HTML.

    Good to use with HTML Portlet

    Format HTML Portlets

    Make sure that fonts etc. fit.

    HTML Portlet

    Begin

    "<html><head></head><body style='font-size: 12px; font-family: tahoma,arial,helvetica,sans-serif;'>"

    End

    "</body><html>"


    Choose Holiday or normal

    Select each using Parameter

    all Types 

    (#XI.TISPar("Distinguish normal days from  holidays")# == false) 
    OR (#XI.TISPar("caclulate holidays instead of normal days")# == false AND  BeginsWith(@Day type@, "Normal")) 
    OR (#XI.TISPar("caclulate holidays instead of normal days")# == true AND  BeginsWith(@Day type@, "Holiday"))
    IN GERMAN: 
    (#XI.TISPar("Feiertage von normalen Tagen unterscheiden")# == false) 
    OR (#XI.TISPar("Feiertage statt normale Tage auswerten")# == false AND  BeginsWith(@Tagestyp@, "Normal"))
    OR (#XI.TISPar("Feiertage statt normale Tage auswerten")# == true AND  BeginsWith(@Tagestyp@, "Feiertag"))


    Structure FROM

    For Access via SQL

    Text

      XIH.IsNull("Path") == false  && XIH.IsNull("Table") == false  ?
      (" FROM " + XIH.TISTable(#Path#,#Table#)  + " ") :
      (XIH.EqualsPrev("dataset") &&
        XIH.EqualsPrev("date") ?
          XIH.GetString("FROM",-1) : "")


    Nice Table

    for Help

    Text

    <html>
    <table border='1' bordercolor='#E8E8E8' cellpadding='5' cellspacing='0' width='100%'> 
    <colgroup> 
    <col width='30%' align='left'> 
    <col width='20%' align='right'>
    </colgroup> 
    <thead bgcolor='#C0C0C0'>
    <tr><th>Name Eingabewert</th><th align='right'>Wert</th></thead> <tr bgcolor='#FFFFFF'> 
    <tr><td>Eingabe</td>
    <td align='right'>Wer</td></td> <tr><td>Eingabe</td><td align='right'>Wer</td></td> </table></html>


    Calculate with times  

    E.g., for calculations with a formula if the operator General calendar is not applicable

    Text

      Code for weekday:
      ""
      + (((int) #Von#.DayOfWeek) == 1 ? "1 Mo" : "" )
      + (((int) #Von#.DayOfWeek) == 2 ? "2 Di" : "" )
      + (((int) #Von#.DayOfWeek) == 3 ? "3 Mi" : "" )
      + (((int) #Von#.DayOfWeek) == 4 ? "4 Do" : "" )
      + (((int) #Von#.DayOfWeek) == 5 ? "5 Fr" : "" )
      + (((int) #Von#.DayOfWeek) == 6 ? "6 Sa" : "" )
      + (((int) #Von#.DayOfWeek) == 0 ? "7 So" : "" )

      FOR TIME: #FROM#.Hour.ToString("00") + ":" + #FROM#.Minute.ToString("00") 

      FOR YEAR: (#FROM#.Year).ToString("0000")  FOR Quarter: "Quarter, " + (#FROM#.Year).ToString("0000") +

    "/" + System.Convert.ToString(System.Math.Floor((#FROM#.Month - 1.0)/3.0)+1.0)   

     
    CODE FOR CALENCAR WEEK:

      (#FROM#.Year).ToString("0000") + ", " +
      (System.Math.Ceiling(
      ((System.Convert.ToDouble(System.DateTime.ParseExact((#FROM#).Year.ToString("0000") 
    + "0101","yyyyMMdd",null).DayOfWeek)) + #FROM#.DayOfYear - 1) / 7.0)).ToString("00")



    Troubleshooting

    General

    Problem

    Frequent Cause

    Solutions

    Formula operator stops

    There is some value that cannot be dealt with, e.g., a wrong date format

    See How to find a row that causes the Formula Operator to stop

    Calculation is not working at all

    1. Common cause: The formula operator is linked to a column header which no longer exists (the column was deleted or renamed) or a column was added but not passed over several formula operators.
    2. Less common cause: The formula operator is linked to a column which uses „ in the column heading
    1. Open the formula operator and the select "edit" where you see the list of column names. The list is updated and this often solves the problem.
    2. Rename column(s).

    Column name is not accepted

    Instead of one blank there are several blanks 

    Rename column

    Calculation yields wrong result (produces integers instead of decimal numbers)


    The C# Compiler work as follows: Integer divided by integer produces integer.
    Example: 5 / 2 = 2 (rounded down).

    If you want a decimal as result, the data must be converted to Double.
    Example: 5.0/2.0 =2.5

    (double) #Integer column# converts an integer to a decimal.

    Calculation yields a much too high result

    Numbers are concatenated as text, then reconverted to numbers.

    Example:

    #DayNo# was exported as text, e.g., 19 from General calendar, but in the formula operator: 

    120.00 + #DayNo# equals 12,019.00; 

    120.00 + System Convert.ToInt32(#DayNo#) equals 139.00.

    Cause:

    • The type of DayNo is string, so 120.00 is first converted to text,
    • this text is then combined with a + (string concat!) and – since the result type is presumably a number – reconverted to a number.
    • With System.Convert.ToInt32 the formula operator is explicitly informed prior to the summation, that the +-operator must be used for numbers, because all data types involved are numbers.

    For 120.00 it would be good to use System.Convert.ToDouble for conversion, so that the formula operator does not use the +-operator for integers.

    Error Message Input string not in correct format

    Presumably, the formula yields a text result. BUT the result format in the operator is not set to text, but, e.g., to Integer.

    Set the result format to Text.

    Operator outputs error message „invalid cast from int32 to DateTime”

    Presumably, values in a data column are missing and a „missing value“ is interpreted as 0.


    Operator outputs error message “invalid cast ...”

    In a column there are values of a type that is not expected (e.g., a number where a text is expected).


    Columns disappear

    This is often due to wrong settings of the columns which shall be transferred.

    Change column settings (see above).

    Unclear error messages

    Possible causes

    1. A point (dot) in the column name
    2. Double space (two blanks) in a column name
    3. Missing values (null values) in rows. E.g., when calculating a value of a preceding record.
    4. Number with decimal places has been written in German (e.g., 1,8) and not English spelling (1.8).
    5. A comment field in the formula operator /* text */ contains three dots in a row (“…”).

    May be try How to find a row that causes the Formula Operator to stop


    Editing

    Problem

    Frequent Cause

    Solutions

    Formula gets very long


    Measures against long formulas

    1. Add comments /*This is an example comment*/
    2. Use breaks and blank spaces for formatting, as in the following example

    Input of decimal numbers


    Numbers with decimal places need to be written with decimal points (e.g., 0.5).

    Apart from that, fractions with decimal numbers need to be spelled in the language used in the TIS system, e.g., 0,5 in German TIS. Otherwise, 0.5 is interpreted as 5, since in the German culture there are numbers using points (e.g., 1.333.222,12) where the points need to be ignored during the reading process.


    Using Parameters in the Formula Operator

    please see General Parameters in the "Formula Operator v04"

    Related topics