`g_ucnt(G;S;X)`

Returns the number of distinct values within a given group.

Function type

Vector only

Syntax

g_ucnt(G;S;X)

Input

Argument Type Description

Argument	Type	Description
`G`	any	A space- or comma-separated list of column names Rows are in the same group if their values for all of the columns listed in `G` are the same. If `G` is omitted, all rows are considered to be in the same group. If any of the columns listed in `G` contain N/A, the N/A value is considered a valid grouping value.
`S`	integer	The name of a column in which every row evaluates to a 1 or 0, which determines whether or not that row is selected to be included in the calculation If `S` is omitted, all rows will be considered by the function (subject to any prior row selections). If any of the values in `S` are neither 1 nor 0, an error is returned.
`X`	any simple type	A column name N/As in `X` are ignored (i.e., the result is the number of the distinct non-N/A values).

G

any

A space- or comma-separated list of column names

Rows are in the same group if their values for all of the columns listed in G are the same.

If G is omitted, all rows are considered to be in the same group.

If any of the columns listed in G contain N/A, the N/A value is considered a valid grouping value.

S

integer

The name of a column in which every row evaluates to a 1 or 0, which determines whether or not that row is selected to be included in the calculation

If S is omitted, all rows will be considered by the function (subject to any prior row selections).

If any of the values in S are neither 1 nor 0, an error is returned.

X

any simple type

A column name

N/As in X are ignored (i.e., the result is the number of the distinct non-N/A values).

Return Value

For every row in each group defined by G (and for those rows where S=1, if specified), g_ucnt returns the integer value corresponding to the number of distinct values in the column listed in X that are in the same group as that row.

If all values for a particular group are N/A, the result is 0.

Sample Usage

<base table="pub.doc.samples.ref.func.g_func_sample_usage"/>
<willbe name="g_ucnt_1" value="g_ucnt(state;include;value)"/>
<willbe name="g_ucnt_2" value="g_ucnt(state city;include;value)"/>

Example

Let's say we want to know how many unique transactions occurred at every store in our Sales Item Detail table (pub.demo.retail.item).

<base table="pub.demo.retail.item"/>

Create a computed column using the g_ucnt(G;S;X) function. Specify store as the group (G), since we're grouping by store; specify transid as X since we want the number of unique transactions; and leave the selection parameter (S) blank, since we want to take into account all of the transactions (not just a subset of those transactions):

<willbe name="numtrans" value="g_ucnt(store;;transid)" label="# of Unique`Transactions"/>

Since we didn't specify a selection column, the numtrans column will show the total number of unique transactions for every store.

Now let's see if we can find out how many unique transactions occurred at each store between 6/1/2012 and 6/30/2012. We essentially want to find out the same information, but for a subset of our data. For this, we can use the selection column parameter of the g_ucnt(G;S;X) function.

Create a computed column that evaluates to true if the transaction date falls between those dates:

<willbe name="in_june" value="between(date;'6/1/2012';'6/30/2012')" label="In June"/>

Now, use the in_june computed column as the selection column in the g_ucnt(G;S;X) function:

<willbe name="numtrans_in_june" value="g_ucnt(store;in_june;transid)" label="# of Unique`Transactions`In June"/>

The numtrans_in_june column shows the total number of unique transactions that occurred between 6/1/2012 and 6/30/2012 for every store.