TableCalc

The TableCalc function is only supported in Studio. It allows the aggregation of one or two tables into a destination table.

The TableCalc function returns a table with records grouped together based on the values in one or more columns, If the GroupBy parameter is passed; otherwise it returns a table with records based on the calculation (Fields parameter) parameters only.

Prototype

    TableCalc(TableA ; TableB; TableDest; GroupBy; Computations; ...) 

Arguments

    TableA        Table      By value   

    A valid variable of type SFHub Table containing the rows to be aggregated.

    TableB        Table      By value   

    A valid variable of type SFHub Table containing the rows to be aggregated.

Note

Either TableA or TableB can be null -- at least one table (A or B) must be defined.

    TableDest         String            By value    

    A valid variable of type SFHub Table designed to store the results of the required aggregation.
    The number of records returned will vary according to the input parameters.

    GroupBy           SFHub List        By value    

    A valid SFHub List of strings containing the column names. Source
    table(s) will be grouped according to the GroupBy list, if more than one
    element is defined, e.g. a sequence of groups is defined, all input rows
    will be aggregated (as defined by the Computations parameter) at each
    group level.

    If you need to group input rows by a combination of keys, then enter the
    column names separated by comma.

Note

GroupBy[] = CollectionCreate( ; ''; 'ColumnA' ; 'ColumnB' ; 'ColumnC')

If different from defining a combination of keys

GroupBy[] = CollectionCreate( ; ''; 'ColumnA,ColumnB,ColumnC')

In the first case TableCalc will first aggregate by ColumnA, then by ColumnB and finally by ColumnC, e.g. you will obtain 3 levels of computation

In the second case TableCals will create one aggregation group only composed by ColumnA, ColumnB and ColumnC.

Empty source table(s) will not be grouped.

If GroupBy is empty, all calculations will be applied without grouping the source table(s)

    Computation      SFHub List        By value

    The calculation to be performed on the input tables (TableA and TableB).
    A variable number of computations (SFHub List) is accepted to perform multiple
    calculations.
    
    At least a computation parameter must be passed to the function.

    Each computation is passed to the function via a SFHub List of strings.
    The SFHub List must contains three strings:

    String1: Column name of the destination table where to store the result.

    String2: The operator used for the calculation. Accepted operators are
             according the below table of operators.

    String3: The column name of the source table used for the calculation.

    If you need to copy other fields from the input table to the destination
    table, a special calculation must be added to the Computation
    parameter. To copy additional fields, from the input table to the
    destination table, add the following List of strings:

    String1: `$CopyField` - CASE SENSITIVE

    String2:  First Column name to be copied.

    StringN:  nth-column name to be copied.

Operator	Description
Sum	Compute the sum of the value
Min	Calculate the minimum value
Max	Calculate the maximum value
Avg	Calculate the average

Returns

    Boolean

    Returns true if the function completes successfully

Exception

    Undefined

    Undefined is returned if:

    - No source tables; both TableA and TableB are not defined or they are not of data type Table
    - Destination table is not defined or is not of data type Table
    - Calculation Fields are not defined or not of the proper type

Example

  TableCalc($TableA ; ; $TableDest ; $GroupBy ; $Fields)

This will return [the Destination Table with new records] aggregated by $GroupBy groups containing all the calculated values defined by $Fields.

$TableA is of type Table and contains the following columns:

Qty of type number
Material of type string
TypeCode of type string
Density of type number

$TableDest is of type Table and contains the following columns:

TotQty of type number
Material of type string
TypeCode of type string

$GroupBy is of type SFHub List and contains:

Material

$Computation is of type SFHub List and contains:

TotQty
Sum
Qty

  __=TableCalc($TableA; ; $TableDest; 
            CollectionCreate(false; ''; 'Material' );             // GroupBy parameter
            CollectionCreate(false; ''; 'TotQty'; 'Sum'; 'Qty'))  // Computation parameter

If you also want to copy the TypeCode column in every aggregation records, you have to insert an additional calculation using the special operator 'CopyField'.

  $$rc=TableCalc($TableA; ; $TableDest ;
            CollectionCreate(false; ''; 'Material');               // GroupBy parameter 
            CollectionCreate(false; ''; 'TotQty'; 'Sum' ; 'Qty');  // Computation parameter
            CollectionCreate(false; ''; '$CopyField'; 'TypeCode')) // Copy column

TableCalc

Example ​

Example