Page tree

 

Support | BlogContact Us | 844.332.2821

 

Contents:

This documentation applies to Trifacta Wrangler. Download this free product.
Registered users of this product or Trifacta Wrangler Enterprise should login to Product Docs through the application.

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 COUNTA.

Basic Usage

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 postal_code value.

Syntax

aggregate value:COUNTA(function_col_ref) [group:group_col_ref]

ArgumentRequired?Data TypeDescription
function_col_refYstringName of column to which to apply the function


For more information on the group parameter, see Aggregate Transform.

function_col_ref

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.

Usage Notes:

Required?Data TypeExample Value
YesString (column reference)myValues

Examples

Example - Simple row count

This section provides simple examples for how to use the COUNTA and 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

Source:

In the following example, the seventh row is an empty string, and the eighth row is a null value.

rowIdVal
r001val1
r002val1
r003val1
r004val2
r005val2
r006val3
r007(empty)
r008(null)

Transform:

Apply a COUNTA function on the source column:

derive value:COUNTA(Val) as:'fctnCounta'

Apply a COUNTDISTINCT function on the source:

derive value:COUNTDISTINCT(Val) as:'fctnCountdistinct'

 

Results:


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.

rowIdValfctnCountdistinctfctnCounta
r001val147
r002val147
r003val147
r004val247
r005val247
r006val347
r007(empty)47
r008(null)47

 

Your Rating: Results: PatheticBadOKGoodOutstanding! 2 rates

This page has no comments.