Aggregating data - Data360_Analyze - Latest

Built-in variables

The following built-in variables can be used to trigger a specific type of output and are commonly used with a where or an if statement:

• firstExec – identifies the first executed record and emits or excludes it depending on your Data360 Analyze Script. Has a value of true the first time the Data360 Analyze Script is executed.
• firstInGroup – true if the current record is the first record in the group.
• lastInGroup – true if the current record is the last record in the group.

You want to calculate the group sum 1, 2, 4, 6, and 10. The Data360 Analyze Script incrementally sums the numbers, ending with the total of 23. By using the lastInGroup function, it tells the node to output the last iteration which in this case is the number 23. The opposite would be true for the variable firstInGroup. When using that variable, the node outputs the first iteration, which in this example would be the sum of the first two numbers, 3.

The first row of your data set is a "Test Account" row. To exclude this initial "test" record from the output, you could use the firstExec variable as follows:

emit *where not firstExec

You can also use execCount to count the total number of records processed. This particular built in variable cannot be used within a where statement. It can, however, be used in a variable as follows:

RecordID = execCountemit *, RecordID

These built-in variables are generally used in the Agg Ex (Deprecated) node.

For further information, see Built-in variables

Built-in macros

You can use the following built-in macros to perform a number of tasks:

• Counting data - groupCount() counts the number of instances of a value in a specified field for the selected group, see groupCount.

  countVariable = groupCount([expression])

  The expression is optional, and can be a field name or a function.

  totalRecordsInGroup = groupCount()totalRecordsThatAreNotNull = groupCount(field.isNotNull())emit *, totalRecordsInGroup, totalRecordsThatAreNotNullwherelastInGroup

• Summarizing data - groupSum() totals the value amounts of a specified field for the selected group, see groupSum.

  countVariable = groupSum(expression)

  The expression is mandatory and can be a field name or a function.

  totalSalesForRep = groupSum(sales)totalCommissionForRep = groupSum(sales * commissionPercent)emit *, totalSalesForRep, totalCommissionForRepwherelastInGroup

• Grouping strings - groupString() lists all values of a specified field in a single row, see groupString.

  groupStringVariable = groupString(expression, [delimiter])

  The expression is mandatory and can be a field name or a function. The optional delimiter can be used to separate the list by the specified delimiter value.

  repsByRegion = groupString(repName, “,”)emit *, repsByRegionwherelastInGroup

• Finding the largest value in a group - groupMax() outputs the maximum value in a specified field for the selected group, see groupMax.

  groupMaxVariable = groupMax(expression)

  The expression is mandatory and can be a field name or a function.

  bestROI = groupMax(return / investment)emit *, bestROIwherelastInGroup

• Finding the smallest value in a group - groupMin() outputs the minimum value in a specified field for the selected group, see groupMin.

  groupMinVariable = groupMin(expression)

  The expression is mandatory and can be a field name or a function.

  lowestMarginInGroup = groupMin((revenue – sales) / revenue)emit *, lowestMarginInGroupwherelastInGroup