Generates the count of non-null rows in a specified column, optionally counted by group. Generated value is of Integer type.
NOTE: Empty string values are counted. Null values are not counted.
NOTE: When added to a transform, the
COUNTA function calculates the number of values in the specified column, as displayed in the current sample. Counts are not applied to the entire dataset until you run the job. 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 value for the already computed instance of
aggregate value: COUNTA(name) group:postal_code
Output: Generates a two-column table containing the unique values for
postal_code and the count of non-empty values in the
name column for that
aggregate value:COUNTA(function_col_ref) [group:group_col_ref]
|function_col_ref||Y||string||Name of column to which to apply the function|
For more information on the
group parameter, see Aggregate Transform.
Name of the column from which to count values based on the grouping.
- Literal values are not supported as inputs.
- Multiple columns and wildcards are not supported.
|Required?||Data Type||Example Value|
|Yes||String (column reference)|
Example - Simple row count
This section provides simple examples for how to use the
COUNTDISTINCT functions. These functions include the following:
COUNTA- Count the number of values within a group that meet a specific condition. See COUNTA Function.
COUNTDISTINCT- Count the number of non-null values within a group that meet a specific condition. See COUNTDISTINCT Function.
In the following example, the seventh row is an empty string, and the eighth row is a null value.
COUNTA function on the source column:
derive value:COUNTA(Val) as:'fctnCounta'
COUNTDISTINCTfunction on the source:
derive value:COUNTDISTINCT(Val) as:'fctnCountdistinct'
Below, both functions count the number of values in the column, with
COUNTDISTINCT counting distinct values only. The empty value for
r007 is counted by both functions.