GROUPBY_WHERE macro

The GROUPBY_WHERE macro is available only in HCL® Campaign.

Syntax

GROUPBY_WHERE(group_field, keyword, rolled_field, where_value [,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.

  • rolled_field

    Specifies the variable to be summarized or rolled up.

  • where_value

    An expression that evaluate to a one or zero value that specifies which rows are to be included in the roll-up operation.

  • 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_WHERE summarizes across specific 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, filtered by the where_value. Only rows with a where_value of one are included in the calculation.

If the optional output_field is not supplied, then the result 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.

Note: See GROUPBY macro for more information on valid values for keyword.

Examples

GROUPBY_WHERE (Household_ID, SumOf, Account_Balance, Account_Balance>0)

Computes the sum of all accounts with positive balances for each household.
GROUPBY_WHERE (Cust_ID, AvgOf, Purchase_Amt,Date(Current_Date) - Date(Purchase_Date)<90)

Computes the average purchase amount for each customer for purchases in the last 90 days.