GROUPBY macro
The GROUPBY
macro
is available only in Unica 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
, andMedianOf
.
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.
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. |