Built-in macros - Data360_Analyze - Latest

groupCount()

groupCount, commonly used with the Agg Ex (Deprecated) node, can be used to count the number of instances of a value for each specified field.

The groupCount macro should be used in the following format, where filter is an optional boolean expression specifying what to count and accumulatorVariable is the name of the variable used to store interim results:

groupCount([filter[, accumulatorVariable]])

If the optional filter expression is given, when this value is encountered, the count is incremented.

If a field is specified in the GroupBy property, the data is grouped by the specified value and all instances of that value are counted. If two fields are specified in the GroupBy property, the groupCount function will only count values that match across the two fields. If only one field is specified in the GroupBy property, all instances of each value in that field will be counted regardless of whether the corresponding values in other fields match or not.

Using groupCount() with a Transform (Deprecated) node

You have the following input data and want to count the number of rows:

In the Script property, enter:

You can use the optional accumulator variable to explicitly control the accumulation and reset of accumulation.

You have the following input data:

In the GroupBy property, enter:

type

In the Script property, enter:

if ('reset') then groupVar = 0rows = groupCount('include' == true, groupVar)emit rows, type

The output would be:

groupMin()

Returns the minimum value of the expression attained during execution within the current execution group. If initialValue is specified it will use that as the initial value to compare to.

The groupMin macro should be used in the following format, where the expression is often a column or variable, the initialValue is an optional expression, which if present, will be used as the initial value to compare to and accumulator variable is the name of the variable used to store interim results:

groupMin(expression[, initialValue[, accumulator variable]])

Given the following input data:

minrand = groupMin(rand, 200.0) emit minrand

The output would be as follows:

In the following example, consider the same input data as in the previous example, but in this case the macro statement does not contain an initial value, as follows:

minrand = groupMin(rand)emit minrand

In this case, the output would be as follows:

In this example, the optional accumulator variable is used with the groupMin macro to explicitly control the accumulation and reset of accumulation, that is, you can control when to reset the concatenation of values..

You have the following input data:

Specify the following groupMin macro statement:

minrand = groupMin(rand, 20, minVar)

To explicitly reset the accumulator variable "minVar" if it drops below 1, you would enter the following Data360 Analyze Script:

if (minVar < 1) then minVar = 1emit minrand

In this case, the output would be as follows:

groupMax()

The groupMax() macro is generally used to find the maximum values in a given field. For example, groupMax() can be used to group your input data by customer, then find the maximum amount paid by each customer.

You can also use groupMax() with a Transform (Deprecated) node, for example, to find the largest amount paid by any customer.

This macro should be used in the following format, where initialValue is optional and if specified will be used as the first value to compare to, expression references a field in the input data and accumulatorVariable is the name of the variable used to store interim results.

groupMax(expression[, initialValue[, accumulatorVariable]])

Using groupMax() with an Agg Ex (Deprecated) node

You have the following input data:

Using an Agg Ex (Deprecated) node, group the data by customer name, then find the maximum amount paid by each customer.

In the GroupBy property, enter Customer name.

In the Script property, enter:

_GroupMax = groupMax(Payments)

emit referencedFields(1,{{^GroupBy^}}),_GroupMax

where lastInGroup

The following data is output:

In this example, the '_GroupMax' column shows the highest amount paid by each customer.

Using groupMax() with a Transform (Deprecated) node

You have the following input data:

Using a Transform (Deprecated) node and the groupMax() macro, enter the following in the Script property:

_maxPayment= groupMax('Payments')

emit 'Customer name','Payments', _maxPayment

The following data is output:

In this example, the groupMax() values are listed in the '_maxPayment' column. As the records are processed, each new value is compared to the first value. If the value is less than the first value, there is no change and the first value is still used. When a value that is higher than the first value is encountered, the higher value is listed.

Specifying an initial value

You have the following input data:

In a Transform (Deprecated) node, you specify the following in the Script property:

maxrand = groupMax(rand, 10000) emit maxrand

The output would be:

With the same input data, if you did not specify an initial value in the Script property, the output would instead be as follows:

groupString()

Generally, the groupString() macro is used with the Transform (Deprecated) or Agg Ex (Deprecated) nodes. It is used to concatenate the values from a given field, and must be used with string data. The value from the first row of data is added to the second row, then the two second row values are added to the third row, and so on. Optionally, you can specify an accumulator variable to define when to reset the accumulation. This macro may be useful if you want to reduce the number of rows across which the string data is spread.

This macro should be used in the following format:

groupString( value [, separator[, accumulatorVariable]])

Where:

• value must be a string data type, and is commonly an input field name.
• separator is an optional string value that indicates the character to use to separate each of the concatenated strings, for example, a comma.
• accumulatorVariable is the name of the variable used to store interim results.

Using groupString() with a Transform (Deprecated) node

You have the following input data:

In the Script property, enter the following:

strColor = groupString(color, ", ") emit strColor

The output would be:

Using groupString() with an Agg Ex (Deprecated) node

You have the following input data:

In the GroupBy property, enter type.

In the Script property, enter the following:

strColor = groupString(color, ", ") emit type, strColor

The output would be:

In this example, you have the same input data as in the previous example and the same field is specified in the GroupBy property. This time, in the Script property, enter the following:

strColor = groupString(color, ", ") emit type, strColor where lastInGroup

The output would be:

Using the accumulator variable

The accumulator variable can be used to explicitly control the accumulation and reset of accumulation, that is, you can control when to reset the concatenation of values..

You have the following input data to a Transform (Deprecated)node:

In the Script property, enter the following:

if ('reset') thencatVal = ""total = groupString(color, ", ", catVal)emit total

The output would be as follows:

groupSum()

groupSum(), commonly used with the Agg Ex (Deprecated) node, produces a sum of all matching values based on the field(s) specified in the GroupBy property. If the data type of the specified field is numeric, the numerical sum is returned. For string data sets, the values are concatenated.

The groupSum() macro should be used in the following format:

groupSum(value [, initialValue[, accumulatorVariable]])

Where:

• value is usually a field name
• initialValue is an optional expression which, if specified, is used as the initial value of the sum
• accumulatorVariable is optional and is the name of the variable that is used to store interim results

Using groupSum() with numerical data

You have the following input data:

In the Script property, enter the following:

total = groupSum(id) emit total

The output would be:

Using groupSum() with string data

You have the following input data:

In the Script property, enter the following:

total = groupSum(color, "Colors: ") emit total

The output would be:

Using the accumulator variable

You can use the optional accumulator variable with the groupSum() macro to explicitly control the accumulation and reset of accumulation, that is, you can control when to reset the concatenation of values.

You have the following input data:

In the Script property, enter the following:

if ('reset') thensumVar = "Colors: ")total = groupSum(color, "Colors: ", sumVar)emit total

The output would be: