Page tree

Versions Compared

Key

  • This line was added.
  • This line was removed.
  • Formatting was changed.
Comment: Published by Scroll Versions from space DEV and version r0710

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)

Transformation:

Apply a COUNTA function on the source column:

D trans
RawWrangletrue
p03Value'fctnCounta'
Typestep
WrangleTextderive type:single value:COUNTA(Val) as:'fctnCounta'
p01NameFormula type
p01ValueSingle row formula
p02NameFormula
p02ValueCOUNTA(Val)
p03NameNew column name
SearchTermNew formula

Apply a COUNTDISTINCT function on the source:

D trans
RawWrangletrue
p03Value'fctnCountdistinct'
Typestep
WrangleTextderive type:single value:COUNTDISTINCT(Val) as:'fctnCountdistinct'
p01NameFormula type
p01ValueSingle row formula
p02NameFormula
p02ValueCOUNTDISTINCT(Val)
p03NameNew column name
SearchTermNew formula

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