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.