Table of Contents
GROUP
Category: Transform / Advanced
Description
Replace multiple values with a single value (constant or calculated).
Use cases
- Creating flags that can later be used in expressions to handle the values within the group (set). This can be more convenient than using lengthy if..then..else statements to group a set of values.
- Group values for charts (e.g. changing insignificant dimension values to "Other")
- Calculating new values of a column only for particular values in another column, leaving the rest unchanged.
- Create secondary demographic sets, combining specified values into rollup groups.
Action settings
| Setting | Description |
|---|---|
| Create/Replace column | Select whether the results will replace the values in an existing column or be created in a new column. Options: Create new column (and provide a column name), or Replace existing (and select an existing column). |
| Group (flag) as* | Provide a static value or expression that will be used as the flag value. All selected values will be converted to this value. Options: It's an expression or It's a text or number. |
| Lookup column | Select the column with the values to be grouped. Once chosen, a list of values present in the column will be displayed at the bottom of the action settings pane. |
| Grouping | Choose whether the selected values will be replaced with the group (flag) value, or whether all values except the selected values will be replaced. Options: Group selected, or Group all but selected. |
| Value selection | Select the values to be replaced, or excluded from replacement, in the list. |
* Setting can be specified using a parameter.
Remarks
Values that are not converted to the group (flag) value retain their original value when using the Group selected option and selecting values.
Use the Group selected option, and select values to evaluate when dealing with an expected set of incoming values (see "Status" column in Example #1, below). When dealing with a column containing varying values (e.g. calculated values), use the "Group all but selected" option and do not select any values to evaluate any/all values that pass into this action (see Example #2, below).
When using an expression for the Group (flag) as setting in conjunction with the Group selected option (and selected values), values that are not flagged by the expression retain their original values. However, when using an expression in conjunction with the Group all but selected option (with no selections), the expression determines the output (see Example #2 below).
Examples
Example #1
Flag accounts that are past due.
Before (source table)
| Customer | Status |
|---|---|
| Alice | Overdue |
| Pierre | Paid |
| Iryna | Past due |
| Michael | Pending |
After (result table)
| Customer | Status | Late flag |
|---|---|---|
| Alice | Overdue | Late |
| Pierre | Paid | Paid |
| Iryna | Past due | Late |
| Michael | Pending | Pending |
Action parameters
Creaate new column: Late flag
Group (flag) as: Late
Group selected (Overdue, Past due)
Example #2
Flag transactions that exceed $5000 as "High Risk" for further review.
Before (source table)
| Transaction ID | Customer | Amount ($) |
|---|---|---|
| T001 | John | 3200 |
| T002 | Emma | 7500 |
| T003 | Shira | 4200 |
| T004 | Michael | 5500 |
After (result table)
| Transaction ID | Customer | Amount ($) | Review |
|---|---|---|---|
| T001 | John | 3200 | |
| T002 | Emma | 7500 | High Risk |
| T003 | Shira | 4200 | |
| T004 | Michael | 5500 | High Risk |
Action parameters
Create new column: Review
Group (flag) as: (expression) if [Amount ($)] >=5000 then "High Risk"
Group all but selected (None selected)