Date: Wed, 26 Jan 2022 07:52:21 +0000 (GMT) Message-ID: <275036405.123188.1643183541691@9c5033e110b2> Subject: Exported From Confluence MIME-Version: 1.0 Content-Type: multipart/related; boundary="----=_Part_123187_1926911363.1643183541691" ------=_Part_123187_1926911363.1643183541691 Content-Type: text/html; charset=UTF-8 Content-Transfer-Encoding: quoted-printable Content-Location: file:///C:/exported.html EXAMPLE - COUNT Functions

EXAMPLE - COUNT Functions

This example demonstrates how to count the number of values and non= -null values within a group.

Functions:

=20 =20 =20 =20 =20 =20 =20 =20 =20
Item Description
COUNTA Function Generates the count of non-null rows in a speci= fied column, optionally counted by group. Generated value is of Integer typ= e.
COUNTDISTINCT Function Generates the count of distinct values in a spe= cified column, optionally counted by group. Generated value is of Integer t= ype.

Source:

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

rowId Val
r001 val1
r002 val1
r003 val1
r004 val2
r005 val2
r006 val3
r007 (empty)
r008 (null)

Transformation:

Apply a COUNTA function on the source column:

=20
=20 =20 =20 =20 =20 =20 =20 =20 =20 =20 =20 =20 =20 =20 =20 =20 =20 =20 =20
Transformation Name <= code>New formula Single row formula COUNTA(Val) 'fctnCounta'
=20

Apply a COUNTDISTINCT function on the source:

=20
=20 =20 =20 =20 =20 =20 =20 =20 =20 =20 =20 =20 =20 =20 =20 =20 =20 =20 =20
Transformation Name <= code>New formula Single row formula COUNTDISTINCT(Val) 'fctnCountdistinct'
=20

Results:

Below, both functions count the number of values in the column, with&nbs= p;COUNTDISTINCT counting distinct values only. The empty value= for r007 is counted by both functions.

rowId Val fctnCountdistinct fctnCounta
r001 val1 4 7
r002 val1 4 7
r003 val1 4 7
r004 val2 4 7
r005 val2 4 7
r006 val3 4 7
r007 (empty) 4 7
r008 (null) 4 7

=20
=20