Date: Wed, 5 Oct 2022 18:24:49 +0000 (UTC) Message-ID: <1160922036.7037.1664994289350@93e1396c9615> Subject: Exported From Confluence MIME-Version: 1.0 Content-Type: multipart/related; boundary="----=_Part_7036_1670386205.1664994289350" ------=_Part_7036_1670386205.1664994289350 Content-Type: text/html; charset=UTF-8 Content-Transfer-Encoding: quoted-printable Content-Location: file:///C:/exported.html ROLLINGCOUNTA Function

# ROLLINGCOUNTA Function

=20

Contents:

=20

=20
=20
=20

=20
=20
Computes the rolling count of non-null values forward or backward of = the current row within the specified column.

• If an input value is missing or null, it is not factored in the computa= tion. For example, for the first row in the dataset, the rolling count of n= on-null values of previous values is undefined.
• The row from which to extract a value is determined by the order in = which the rows are organized based on the `order` parameter.&nbs= p;If you are working on a randomly generated sample of your dataset, t= he values that you see for this function might not correspond to the values= that are generated on the full dataset during job execution.

• The function takes a column name and two optional integer paramet= ers that determine the window backward and forward of the current row.
• The default integer parameter values are `-1` and=  `0`, which computes the rolling function from the current = row back to the first row of the dataset.
• This function works with the following transforms:

For more information on a non-rolling version of this function, see = ;COUNTA Function.

## Basic = Usage

=20

Column example:

derive type:single value:ROLLINGCOUNTA(myCol)

Output: Generates a new column containing th= e rolling count of non-null values in the `myCol` colu= mn from the first row of the dataset to the current one.

Rows before example:

=20

window value:ROLLINGCOUNTA(myNumber, 3) <= /p>

Output: Generates the new column, which contains the ro= lling count of non-null values of the current row and the two previous row = values in the `myNumber` column.

Rows before and after example:

=20

window value:ROLLINGCOUNTA(myNumber, 3, 2)

Output: Generates the new column, which contains the rolling count of non-nulls from the two previous row= values, the current row value, and the two rows after the current one in t= he `myNumber` column.   &n= bsp;

## Syntax and Arguments

=20

=20

window value:ROLLINGCOUNTA(col_ref,= rowsBefore_integer, rowsAfter_integer) order: order_col [group: grou= p_col]

=20 =20 =20 =20 =20
Argument Required? Data Type Description
col_ref Y string Name of column whose values are applied to the f= unction
rowsBefore_integer N integer Number of rows before the current one to include= in the computation
rowsAfter_integer N integer Number of rows after the current one to include = in the computation

For more information on the `order` and ```= group``` parameters, see Window Transform.

For more information on syntax standards, see Language Documentation Syntax Note= s.

### col_ref

Name of the column whose values are used to compute the function. <= /p>

• Multiple columns and wildcards are not supported.

Usage Notes:

=20

=20 =20 =20 =20
Required? Data Type Example Value
Yes String (column reference to Integer or Decimal v= alues) `myColumn`

### rowsBefore_integer, rowsAfter_integer

Integers representing the number of rows before or after the curre= nt one from which to compute the rolling function, including the current ro= w. For example, if the first value is `5`, the current row = and the four rows after it are used in the computation. Negativ= e values for `k` compute the rolling average from rows= preceding the current one.

• `rowBefore=3D1` generates the current row value only.
• `rowBefore=3D-1`  uses all rows preceding the cur= rent one.
• If `rowsAfter` is not specified, then the value 0 is applied.
• If a `group` parameter is applied, then these parameter= values should be no more than the maximum number of rows in the groups.

Usage Notes:

=20

=20 =20 =20 =20
Required? Data Type Example Value
No Integer `4`

## Examp= les

=20
=20
=20

Tip: For additional examples, see Common Tasks.

=20

### Example - Countin= g messages

In the following example, messages are tabulated every 10 seconds = from a system. If no message is generated, null values are returned. <= /span>

Source:

Timestamp msgType msgDescription
15:10:00 PM warning Server restarted.
15:10:10 PM warning Unable to locate patterns file.
15:10:20 PM
15:10:30 PM
15:10:40 PM error Cannot connect to data source.
15:10:50 PM error Cannot open dataset.
15:11:00 PM
15:11:10 PM
15:11:20 PM error Insufficient permissions to write to target loca= tion.
15:11:30 PM
15:11:40 PM warning Server restarted.
15:11:50 PM warning Unable to locate patterns file.
15:12:00 PM error Data node offline.
15:12:10 PM
15:12:20 PM
15:12:30 PM warning Invalid statement in recipe.
15:12:40 PM
15:12:50 PM

Transform:

You are interested in counting the number of entries for the prece= ding minute for each row. You add the following:

derive type: multiple value: rollingcounta(msgTy= pe, 5, 0) order: Timestamp as: 'rollingcounta_msgType'

Results:

Timestamp msgType msgDescription rollingcounta_msgType
15:10:00 PM warning Server restarted. 1
15:10:10 PM warning Unable to locate patterns file. 2
15:10:20 PM     2
15:10:30 PM
15:10:40 PM error Cannot connect to data source. 3
15:10:50 PM error Cannot open dataset. 4
15:11:00 PM
15:11:10 PM
15:11:20 PM error Insufficient permissions to write = to target location. 3
15:11:30 PM     3
15:11:40 PM warning Server restarted. 3
15:11:50 PM warning Unable to locate patterns file. 3
15:12:00 PM error Data node offline. 4
15:12:10 PM     4
15:12:20 PM     3
15:12:30 PM warning Invalid statement in recipe. 4
15:12:40 PM     3
15:12:50 PM     2

=20
=20

=20

=20
• =20
Page:<= /span>=20
=20
• =20
• =20
Page:<= /span>=20
=20
• =20
• =20
Page:<= /span>=20
=20
• =20
• =20
Page:<= /span>=20
=20
• =20
• =20
Page:<= /span>=20
=20
• =20
• =20
Page:<= /span>=20
=20
• =20
• =20
Page:<= /span>=20
=20
• =20
• =20
Page:<= /span>=20
=20
• =20
• =20
Page:<= /span>=20
=20
• =20
• =20
Page:<= /span>=20
=20
• =20
=20

=20
=20
=20

------=_Part_7036_1670386205.1664994289350--