Aggregate Functions

Aggregate functions do two jobs. They query the records of the current transaction type or a sibling. They also query the values held in a field that an Aggregate transform has set to accumulate values.

• Concatenate
• Count
• DistinctCount
• First
• IsIn
• Last
• Minimum
• Maximum
• Sum

Concatenate

Description

Returns a string that joins the values of an aggregate field, or of a field in the current or a sibling transaction.

Syntax

Concatenate(field, transaction, separator [, testField [, testValue [, testOperator]]] )

Arguments

• field
  • The field whose values are concatenated.
• transaction
  • The transaction to query.
  • To query an aggregate field specify "."; otherwise, specify a valid transaction type.
• testField
  • Optional. Limits the concatenation to values where testField meets the condition set by testValue and testOperator.
• testValue
  • Optional. Used with testField. The value to compare against.
• testOperator
  • Optional. Used with testField. The operator that compares testField with testValue.

Samples

To concatenate a field within a child transaction, separating the values with a space, a dash and a space.

Concatenate("field", "transaction", " – ")

To concatenate a field within an aggregate transaction, separating the values with a comma.

Concatenate("field", ".", ",")

To concatenate the description field on the orderLines transaction where the qty field is equal to zero (0).

Concatenate("description", "orderLines", ",", "qty", 0)

To concatenate the description field on the orderLines transaction where the reference field is not empty.

Concatenate("description", "orderLines", ",", "reference", "", "<>")

To concatenate the description field on the orderLines transaction where the description itself is not empty.

Concatenate("description", "orderLines", ",", "description", "", "neq")

Count

Description

Returns the number of records in the current or a sibling transaction.

Syntax

Count(transaction [, field [, testField [, testValue [, testOperator]]]] )

Arguments

• transaction
  • Must be either the transaction type of the current record or one of its siblings.
• field
  • Optional. The field to count when counting an aggregate field. Omit it when counting child transactions.
• testField
  • Optional. Limits the count to values where testField meets the condition set by testValue and testOperator.
• testValue
  • Optional. Used with testField. The value to compare against.
• testOperator
  • Optional. Used with testField. The operator that compares testField with testValue.

The testOperator values are:

Operation	Symbolic value	Non-symbolic value	Integer value
Equals	=	eq	1
Not Equals	<>	neq	2
Less Than	<	lt	3
Less Than or Equals	<=	lte	4
Greater Than	>	gt	5
Greater Than or Equals	>=	gte	6

Samples

To count the number of records within a child transaction.

Count( "transaction" )

To count the qty field within an aggregate transaction.

Count(".", "qty")

To count the orderLines transaction where the qty field is equal to zero (0). The second argument is an empty string. You must supply it, but the function does not use it.

Count("orderLines", "", "qty", 0)

To count the orderLines transaction where the reference field is not empty.

Count("orderLines", "", "reference", "", "<>")

To count the orderLines transaction where the description itself is not empty.

Count("orderLines", "", "description", "", "neq")

To count the values of the reference aggregate field when the record's status field is not CANCELLED. On an aggregate field the test applies once, to the current record, not to each value.

Count(".", "reference", "status", "CANCELLED", "<>")

DistinctCount

Description

Returns the number of distinct values in the current or a sibling transaction.

Syntax

DistinctCount(field, transaction [, testField [, testValue [, testOperator]]] )

Arguments

• field
  • The field for which the distinct count is maintained.
• Transaction
  • Must be either the transaction type of the current record or one of its siblings.
  • To query an aggregate field specify "."; otherwise, specify a valid transaction type.
• testField
  • Optional. Limits the distinct count to values where testField meets the condition set by testValue and testOperator.
• testValue
  • Optional. Used with testField. The value to compare against.
• testOperator
  • Optional. Used with testField. The operator that compares testField with testValue.

The testOperator values are:

Operation	Symbolic value	Non-symbolic value	Integer value
Equals	=	eq	1
Not Equals	<>	neq	2
Less Than	<	lt	3
Less Than or Equals	<=	lte	4
Greater Than	>	gt	5
Greater Than or Equals	>=	gte	6

Samples

To find the distinct count of a field within a child transaction.

DistinctCount ("field", "transaction")

To find the distinct count of a field within an aggregate transaction.

DistinctCount ("field", ".")

To find the distinct count of the description field on the orderLines transaction where the qty field is equal to zero (0).

DistinctCount("description", "orderLines",  "qty", 0)

To find the distinct count of the description field on the orderLines transaction where the reference field is not empty.

DistinctCount("description", "orderLines", "reference", "", "<>")

To find the distinct count of the description field on the orderLines transaction where the description itself is not empty.

DistinctCount("description", "orderLines", "description", "", "neq")

To find the distinct count of the reference aggregate field when the record's status field is not CANCELLED. On an aggregate field the test applies once, to the current record, not to each value.

DistinctCount("reference", ".", "status", "CANCELLED", "<>")

First

Description

Returns the first value from either:

• Array
  • A value from an array of values.
• Transaction
  • A value in the current or a sibling transaction.

Syntax

First( transactionOrArray [, field [, testField [, testValue [, testOperator]]]] )

Arguments

• transactionOrArray
  • Either the child transaction id or the array to take the first element from.
  • To query an aggregate field specify "."; otherwise, specify a valid transaction type.
  • When you pass an array, the function ignores the field, testField, testValue and testOperation arguments.
• field
  • The field of the child transaction.
• testField
  • Optional. Returns the first value where testField meets the condition set by testValue and testOperator.
• testValue
  • Optional. Used with testField. The value to compare against.
• testOperator
  • Optional. Used with testField. The operator that compares testField with testValue.

Samples

Returns 'first', the first element of the array.

First(Array("first", "second", "third"))

Returns the first element of the array that the Split function returns.

Dim Vals
Vals = Split("first,second,third", ",")
First(Vals)

Returns the description from the first record of the 'lines' transaction.

First("lines", "description")

Returns the first value of the qty aggregate field.

First(".", "qty")

To get the first record's description field on the orderLines transaction where the qty field is equal to zero (0).

First("orderLines", "description", "qty", 0)

Returns the first record's description field on the orderLines transaction where the reference field is not empty.

First("orderLines", "description", "reference", "", "<>")

Returns the first record's description field on the orderLines transaction where the description itself is not empty.

First("orderLines", "description", "description", "", "neq")

IsIn

Description

Returns True or False to show whether a value exists in either:

• Array
  • A value from an array of values.
• Transaction
  • A value in the current or a sibling transaction.

Syntax

IsIn( testValue, transactionOrArray [, field] )

Arguments

• testValue
  • The value to compare to.
• transactionOrArray
  • Either the child transaction id or the array to search for testValue.
  • To query an aggregate field specify "."; otherwise, specify a valid transaction type.
• field
  • Optional. The field of the child transaction. The function ignores it when you pass an array, and requires it when you pass a transaction id.

Samples

Returns True, because 2 is a value in the array.

IsIn(2, Array(0, 1, 2, 2, 9))

Returns True, because "2" is one of the values Split returns. Split returns strings, and a string never equals a number: IsIn(2, Vals) is False. Split also keeps any spaces. Splitting "0, 2" gives " 2", and " 2" does not match "2".

Dim Vals
Vals = Split("9,two,3,three,zero,0,2", ",")
IsIn("2", Vals)

Indicates if any qty field in the lines transaction has a zero (0) value.

IsIn(0, "lines", "qty")

Indicates if any value of the qty aggregate field is zero (0).

IsIn(0, ".", "qty")

Last

Description

Returns the last value from either:

• Array
  • A value from an array of values.
• Transaction
  • A value in the current or a sibling transaction.

Syntax

Last( transactionOrArray [, field [, testField [, testValue [, testOperator]]]] )

Arguments

• transactionOrArray
  • Either the child transaction id or the array to take the last element from.
  • To query an aggregate field specify "."; otherwise, specify a valid transaction type.
  • When you pass an array, the function ignores the field, testField, testValue and testOperation arguments.
• field
  • The field of the child transaction.
• testField
  • Optional. Returns the last value where testField meets the condition set by testValue and testOperator.
• testValue
  • Optional. Used with testField. The value to compare against.
• testOperator
  • Optional. Used with testField. The operator that compares testField with testValue.

Samples

Returns 'third', the last element of the array.

Last(Array("first", "second", "third"))

Returns the last element of the array that the Split function returns.

Dim Vals
Vals = Split("first,second,third", ",")
Last(Vals)

Returns the description from the last record of the 'lines' transaction.

Last ("lines", "description")

Returns the last value of the qty aggregate field.

Last (".", "qty")

To get the last record's description field on the orderLines transaction where the qty field is equal to zero (0).

Last("orderLines", "description", "qty", 0)

Returns the last record's description field on the orderLines transaction where the reference field is not empty.

Last("orderLines", "description", "reference", "", "<>")

Returns the last record's description field on the orderLines transaction where the description itself is not empty.

Last("orderLines", "description", "description", "", "neq")

Minimum

Description

Returns the minimum value of an aggregate field or of a field in the current or sibling transaction.

Syntax

Minimum(field, transaction [, testField [, testValue [, testOperator]]] )

Arguments

• field
  • The field to take the minimum from.
• transaction
  • The transaction to query.
  • To query the aggregate field specify "."; otherwise, specify a valid transaction type.
• testField
  • Optional. Limits the minimum to values where testField meets the condition set by testValue and testOperator.
• testValue
  • Optional. Used with testField. The value to compare against.
• testOperator
  • Optional. Used with testField. The operator that compares testField with testValue.

The testOperator values are:

Operation	Symbolic value	Non-symbolic value	Integer value
Equals	=	eq	1
Not Equals	<>	neq	2
Less Than	<	lt	3
Less Than or Equals	<=	lte	4
Greater Than	>	gt	5
Greater Than or Equals	>=	gte	6

Samples

To find the minimum of a field within a child transaction.

Minimum ("field", "transaction")

To find the minimum of a field within an aggregate transaction.

Minimum ("field", ".")

To find the minimum of the qty field on the orderLines transaction where the item field is equal to "ABC-001".

Minimum ("qty", "orderLines",  "item", "ABC-001")

To find the minimum of the unitPrice field on the orderLines transaction where the item field is not equal to "SHIPPING".

Minimum ("unitPrice", "orderLines",  "item", "SHIPPING", "<>")

To find the minimum of the extendedAmount aggregate field when the record's qty field is greater than 1. On an aggregate field the test applies once, to the current record, not to each value.

Minimum("extendedAmount", ".", "qty", 1, ">")

Maximum

Description

Returns the maximum value of an aggregate field or of a field in the current or sibling transaction.

Syntax

Maximum(field, transaction [, testField [, testValue [, testOperator]]] )

Arguments

• field
  • The field to take the maximum from.
• transaction
  • The transaction to query.
  • To query the aggregate field specify "."; otherwise, specify a valid transaction type.
• testField
  • Optional. Limits the maximum to values where testField meets the condition set by testValue and testOperator.
• testValue
  • Optional. Used with testField. The value to compare against.
• testOperator
  • Optional. Used with testField. The operator that compares testField with testValue.

The testOperator values are:

Operation	Symbolic value	Non-symbolic value	Integer value
Equals	=	eq	1
Not Equals	<>	neq	2
Less Than	<	lt	3
Less Than or Equals	<=	lte	4
Greater Than	>	gt	5
Greater Than or Equals	>=	gte	6

Samples

To find the maximum of a field within a child transaction.

Maximum ("field", "transaction")

To find the maximum of a field within an aggregate transaction.

Maximum ("field", ".")

To find the maximum of the qty field on the orderLines transaction where the item field is equal to "ABC-001".

Maximum ("qty", "orderLines",  "item", "ABC-001")

To find the maximum of the unitPrice field on the orderLines transaction where the item field is not equal to "SHIPPING".

Maximum("unitPrice", "orderLines",  "item", "SHIPPING", "<>")

To find the maximum of the extendedAmount aggregate field when the record's qty field is greater than 0. On an aggregate field the test applies once, to the current record, not to each value.

Maximum("extendedAmount", ".", "qty", 0, ">")

Sum

Description

Returns the sum of the values of an aggregate field or of a field in the current or sibling transaction.

Syntax

Sum(field, transaction [, testField [, testValue [, testOperator]]] )

Arguments

• field
  • The field to sum.
• transaction
  • The transaction to query.
  • To query the aggregate field specify "."; otherwise, specify a valid transaction type.
• testField
  • Optional. Limits the sum to values where testField meets the condition set by testValue and testOperator.
• testValue
  • Optional. Used with testField. The value to compare against.
• testOperator
  • Optional. Used with testField. The operator that compares testField with testValue.

The testOperator values are:

Operation	Symbolic value	Non-symbolic value	Integer value
Equals	=	eq	1
Not Equals	<>	neq	2
Less Than	<	lt	3
Less Than or Equals	<=	lte	4
Greater Than	>	gt	5
Greater Than or Equals	>=	gte	6

Samples

To find the sum of the LINETOTAL field from OrderDetail transaction:

Sum ("LINETOTAL","OrderDetail")

To sum the extendedAmount aggregate field.

Sum ("extendedAmount", ".")

To find the sum of the extendedAmount field on the orderLines transaction where the item field is equal to "SHIPPING".

Sum ("extendedAmount", "orderLines",  "item", "SHIPPING")

To find the sum of the qty field on the orderLines transaction where the item field is not equal to "SHIPPING".

Sum ("qty", "orderLines",  "item", "SHIPPING", "<>")

To sum the extendedAmount aggregate field when the record's qty field is greater than 0. On an aggregate field the test applies once, to the current record, not to each value.

Sum("extendedAmount", ".", "qty", 0, ">")