Generates the count of rows in each group that meet a specific condition. Generated value is of Integer type.
NOTE: When added to a transform, this function is applied to the current sample. If you change your sample or run the job, the computed values for this function are updated. Transforms that change the number of rows in subsequent recipe steps do not affect the values computed for this step.
To perform a simple count of rows without conditionals, use the COUNT function. See COUNT Function.
Basic Usage
pivot value: COUNTIF(failed_deliveries >= 10) group:postal_code limit:1
Output: Generates a two-column table containing the unique values for postal_code and the count of records for that postal_code value in which the value of the failed_deliveries column is greater than or equal to 10. The limit parameter defines the maximum number of output columns.
Syntax and Arguments
pivot value:COUNTIF(test_expression) [group:group_col_ref] [limit:limit_count]
| Argument | Required? | Data Type | Description |
|---|---|---|---|
| test_expression | Y | string | Expression that is evaluated. Must resolve to true or false |
For more information on syntax standards, see Language Documentation Syntax Notes.
For more information on the group and limit parameters, see Pivot Transform.
test_expression
This parameter contains the expression to evaluate. This expression must resolve to a Boolean (true or false) value.
Usage Notes:
| Required? | Data Type | Example Value |
|---|---|---|
| Yes | String expression that evaluates to true or false | (LastName == 'Mouse' && FirstName == 'Mickey')
|
Tip: For additional examples, see Common Tasks.
Examples
Example - COUNTIF Functions
COUNTIF and COUNTIFA functions. These functions include the following:COUNTIF- Count the number of values within a group that meet a specific condition. See COUNTIF Function.COUNTAIF- Count the number of non-null values within a group that meet a specific condition. See COUNTAIF Function.
Source:
The following data identifies sales figures by salespeople for a week:
| EmployeeId | Date | Sales |
|---|---|---|
| S001 | 1/23/17 | 25 |
| S002 | 1/23/17 | 40 |
| S003 | 1/23/17 | 48 |
| S001 | 1/24/17 | 81 |
| S002 | 1/24/17 | 11 |
| S003 | 1/24/17 | 25 |
| S001 | 1/25/17 | 9 |
| S002 | 1/25/17 | 40 |
| S003 | 1/25/17 | |
| S001 | 1/26/17 | 77 |
| S002 | 1/26/17 | 83 |
| S003 | 1/26/17 | |
| S001 | 1/27/17 | 17 |
| S002 | 1/27/17 | 71 |
| S003 | 1/27/17 | 29 |
| S001 | 1/28/17 | |
| S002 | 1/28/17 | |
| S003 | 1/28/17 | 14 |
| S001 | 1/29/17 | 2 |
| S002 | 1/29/17 | 7 |
| S003 | 1/29/17 | 99 |
Transform:
You are interested in the count of dates during the week when each salesperson sold less than 50 units, not factoring the weekend. First, you try the following:
pivot value:COUNTIF(Sales < 50) group:EmployeeId limit:1
WEEKDAY function:
derive type:single value:WEEKDAY(Date) as:'DayOfWeek'
DayOfWeek > 5, then it is a weekend date. For further precision, you can use the COUNTAIF function to remove the nulls:
pivot value:COUNTAIF(Sales, DayOfWeek<6) group:EmployeeId limit:1
Sales when the day of the week is not a weekend day, as grouped by individual employee.
Results:
| EmployeeId | countaif_Sales |
|---|---|
| S001 | 5 |
| S002 | 4 |
| S003 | 4 |
This page has no comments.