GROUPBY macro

The GROUPBY macro is available only in HCL® Campaign.

Syntax

GROUPBY(group_field, keyword, rolled_field [,output_field])

Parameters

  • group_field

    Specifies the variable over which records are grouped (that is, all the same values of the specified variable are grouped together).

  • keyword

    Specifies the summary roll-up function to perform on the rolled-field.

  • rolled_field

    Specifies the variable to be summarized or rolled up.

  • output_field

    Identifies an alternate variable to be returned for a single row of a group and can be used only with the keywords MinOf, MaxOf, and MedianOf.

Description

GROUPBY summarizes across multiple rows of data within a group. The output of this function is a single column. The output is the result of the operation specified by keyword on the rolled_field over the homogeneous group specified by the group_field. If there is more than one answer satisfying a specified condition, the first one encountered is returned.

If the optional output_field is not supplied, then the output is the result of the operation on rolled_field. If output_field is supplied, then the result is the output_field of the row within the group.

If there are multiple rows within a group that satisfy the specified condition (for example, there are ties for the max value), the output-field associated with the first row satisfying the condition is returned.

Note: To work with grouping over multiple columns, you can enclose a list of field names, separated by commas, within a set of "curly" brackets "{ }" and using this as the first parameter in the GROUPBY macro call.

Supported keywords are as follows (case insensitive):

Keyword

String?

Yes/No

Description
CountOf Yes Returns the number of records in each group (rolled_field can be numeric or string; the returned value is the same regardless of the value of rolled_field).
MinOf Yes Returns the minimum value of rolled_field in each group (rolled_field can be numeric or string; if rolled_field is a string, the value closest to the beginning of the alphabet where alphabetically sorted is returned).
MaxOf Yes Returns the maximum value of rolled_field in each group (rolled_field can be numeric or string; if rolled_field is a string, the value closest to the end of the alphabet when alphabetically sorted is returned).
DiffOf Yes Returns the number of distinct values of rolled_field in each group (rolled_field can be numeric or string).
AvgOf No Returns the average value of rolled_field in each group (rolled_field must be numeric).
ModeOf Yes Returns the modal value (that is, the most commonly occurring value) of rolled_field in each group (rolled_field can be numeric or string).
MedianOf Yes Returns the median value (that is, the middle value when sorted by rolled_field) of rolled_field in each group (rolled_field can be numeric or string; if rolled_field is a string, the values are sorted alphabetically).
OrderOf Yes Returns the order of rolled_field in each group (rolled_field must be numeric). If multiple records have the same value, they all receive the same value.
SumOf No Returns the sum of rolled_field in each group (rolled_field must be numeric).
StdevOf No Returns the standard deviation of rolled_field in each group (rolled_field must be numeric).
IndexOf Yes

Returns the 1-based index (ordered by rolled_field) of each record (rolled_field can be numeric or string). The sort order is ascending.

Note: For numeric fields, the sort order of RankOf and IndexOf can be made descending by putting a minus sign (-) in front of the sort field.

RankOf Yes

Returns the 1-based category (ordered by rolled_field) in which each record lies (rolled_field can be numeric or string). The sort order is ascending.

Note: For numeric fields, the sort order of RankOf and IndexOf can be made descending by putting a minus sign (-) in front of the sort field.

Examples

GROUPBY (Household_ID, SumOf, Account_Balance)

Computes the sum of all account balances by household.

GROUPBY (Cust_ID, MinOf, Date(Account_Open_Date), Acc_Num)

Returns the account number of first account opened by customer.