WithGroup

Takes six parameters, FieldBase, GroupByField, Destination, Action, Source, IfLenFld

Description:

The Work Query should be pre-sorted by the Field specified by GroupByField, unless you are looking for the effect of not doing this, perhaps to calculate on sub-groups. Then, for each record where the GroupByField matches the specified Action will be performed on Source and the result will be stored in Destination for every Record of the same Group.

List of Actions:

Usage:

FieldBase, GroupByField, Destination, Action, Source, IfLenFld
[WorkWith], GroupByField, Destination, Action, Source, IfLenFld
[WorkWith], GroupByField, Destination, Action, Source, [""]
[WorkWith], GroupByField, Destination, Action, [""], [""]
FieldBase, GroupByField, Destination, Action, Source, [WorkIf]
[WorkWith], GroupByField, Destination, Action, Source, [WorkIf]
[WorkWith], GroupByField, Destination, Action, [""], [WorkIf]

Example:

Use the images below to understand how this example works, but what is happening here, is we are grouping all of the records in memory by their "Biz.Analysis.Code" and then summing up all of the "Biz.Samples.Qty" in each record. Notice how there is only 1 record for each "Biz.Analysis.Code" with the total summed up for "Biz.Samples.Qty" and "Biz.Samples.Rush"

    Rem 'load data
        LoadTable      "Biz.Job"
        KeepIf         "Biz.Job.Status", "<>", "`Completed"
        Children      "Biz.Samples"
        KeepIf         "Biz.Samples.Status", "<>", "`Completed"
        KeepIf         "Biz.Samples.Status", "<>", "`Batched"
        Children     "Biz.Analysis"
        KeepIf         "Biz.Analysis.Code"
        KeepIf         "Biz.Analysis.Status", "<>", "`Completed"
        KeepIf         "Biz.Analysis.Status", "<>", "`Batched"
    EndRem
    
    Rem 'sort then count
        Sort         "Biz.Analysis.Code"
        SetIf         "Biz.Samples.Rush", "Biz.Samples.Qty", "Biz.Samples.Rush"
        
        Rem 'example input table
            HtmlTable
        EndRem
        
        WithGroup     "Biz.Analysis.Code", "Biz.Samples.Qty", "Sum", "Biz.Samples.Qty"
        WithGroup     "Biz.Analysis.Code", "Biz.Samples.Rush", "Sum", "Biz.Samples.Rush"
        KeepUnique     "Biz.Analysis.Code"    
    EndRem
    
    Rem 'example output table
        HtmlTable
    EndRem

Input Table:

Output Table: