Contents:
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.
| 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:
| Transformation Name | New formula |
|---|---|
| Parameter: Formula type | Single row formula |
| Parameter: Formula | COUNTA(Val) |
| Parameter: New column name | 'fctnCounta' |
Apply a COUNTDISTINCT function on the source:
| Transformation Name | New formula |
|---|---|
| Parameter: Formula type | Single row formula |
| Parameter: Formula | COUNTDISTINCT(Val) |
| Parameter: New column name | '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.
| 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 |
This page has no comments.