Versions Compared

Old Version 49

changes.mady.by.user Lakshmi Manda

Saved on

compared with

New Version 50

changes.mady.by.user Kalpana Malladi

Saved on

Key

  • This line was added.
  • This line was removed.
  • Formatting was changed.

In this article

Table of Contents
maxLevel2
excludeOlder Versions|Additional Resources|Related Links|Related Information

Overview

You can use this Snap to apply aggregate functions on input data using the Group By support. You can calculate perform an aggregate function on a set of values to return a single scalar value.

  • This Snap does not support List list and Map map objects referenced in the JSON paths.

  • If the input documents areunsorted and GROUP-BY fields are used, you must use the Sort Snap upstream of the Aggregate Snap to presort the input document stream and set the Sorted stream field Ascending or Descending to prevent the out-of-memory error. Learn more about presorting unsorted input documents to be processed by the Aggregate Snap.

...

Snap Type

Aggregate Snap is a transofrm Transofrm type Snap that transforms, parses, cleans, and formats data from binary data to document data.

Prerequisites

...

  • Output field

    • Field Name

    Type

    Description

    Label*

    Default Value: Aggregate
    Example: Aggregate_Avg

    String

    Specify the name for the Snap. You can modify this to be more specific, especially if you have more than one of the same Snap in your Pipeline.

    Aggregate fields*

    Use this field set to define the type of Aggregate function to perform on the field and the key name to be used in the output. This field set contains the following fields:

    • Function

    • Field

    • Result field

    Function*

    Default Value: SUM
    Example: AVG

    String

    Select the functions that applies to the aggregate field value in the input data. The available functions and the supported datatypes are: 

    • COUNT – all types.

    • SUM – Number (Numeric string is converted to a number).

    • MIN – Number, String, DateTime, LocalDateTime, LocalDate, LocalTime.

    • MAX – Number, String, DateTime, LocalDateTime, LocalDate, LocalTime.

      • When using a date for MIN/MAX functions, change “$DATE.date" to "$DATE" in the Aggregate field.

    • AVG – Number, DateTime, LocalDateTime, LocalDate, LocalTime (Numeric string is converted to a number).

    • CONCAT – Concatenates all records separated by a pipe (|).

    • UNIQUE_CONCAT – Concatenates all unique records separated by a pipe (|).

    When you select the AVG function, the Snap rounds up all numeric values that have more than 16 digits. The AVG function handles the numeric values as below:

    • If the raw value is less than 16 with or without decimals, then the output value displays without any change.

      • Raw Value: 1234567890/1234567890.1234

      • Output Value: 1234567890/1234567890.1234

    • If the raw value is greater than or equal to 16 with or without decimals, then the output value is rounded off to 16 digits, including decimals.

      • Raw Value: 123456789012345.67

      • Output Value: 123456789012345.7

    • If the raw value is greater than 16 with or without decimals, then the output value is rounded up to 16 digits and expressed in exponential notation.

      • Raw Value: 1234567890123456789/1234567890123456789.123

      • Output Value: 1.234567890123457E+18

    • If the raw value is non-terminating decimal, then the output value is truncated to 16 digits.

      • Raw Value: 1.3333333333333333333333

      • Output Value: 1.333333333333333

    Field*

    Default value: [None]
    Example:  $Total or DateTime.parse($service)

    String/Expression

    Specify a JSON path to the field on which the Aggregate function should be applied such as $items.item. Learn more: JSONPath - XPath for JSON.

    Result field*

    Default value:  [None]
    Example:  Revenue

    String

    Specify the field name to be used for mapped data in the output. This value is the aggregate computed result corresponding to the GROUP-BY field values.

    GROUP-BY fields*

    Use this field set to define field paths and names. If you leave this field blank, the Snap produces only one output document. This field set contains the following fields:

    Field

    Field

    Default value: [None]
    Example:  $.Product.Name

    String/Expression

    Specify a JSON path for the GROUP-BY field.

    Output field

    Default value:  [None]
    Example:  ProductName

    String

    Specify the GROUP-BY field name to be used in the output map data. If left blank, the Field path is used instead.

    Integer mode

    Default value: Deselected
    Example: Selected

    Checkbox

    Select this checkbox if you want the Snap to produce integer results rounded half up.

    The input data can be mixed in integers and floating-point numbers, and the Snap maintains intermediate results in floating-point numbers. The value of this field is ignored in the COUNT Aggregate function.

    Sorted streams*

    Default value: Unsorted
    Example: Ascending

    Dropdown list

    Select an option to specify if the input documents are sorted or not.

    This option enables the Snap to verify if the input is sorted as it processes each document and performs the aggregation efficiently and displays an error if the records are not sorted.

    The available options are:

    • Unsorted: The input documents are not sorted.

    • Ascending: The input documents are sorted in an ascending order.

    • Descending: The input documents are sorted in descending order.

    If the input data stream contains a large number of documents, then presort the input using the Sort Snap as this uses less memory and results in an effective performance. If you select Unsorted and are using GROUP-BY fields, the Aggregate Snap uses MapDB internally in order to avoid an out-of-memory error issue. Therefore, starting with 434patches23034, when GROUP-BY fields are used in the Aggregate Snap and the Sorted streams field is Unsorted, the order of the output documents may be different from one execution to another.

    Minimum memory (MB)

    Default value: 500
    Example: 750

    String/Expression

    If the available memory is less than this property value while processing input documents, the Snap stops to fetch the next input document until more memory is available. This feature is disabled if this property value is 0.

    Out-of-memory timeout (minutes)

    Default value: 30
    Example: 40

    String/Expression

    If the Snap pauses longer than this property value while waiting for more memory available, it throws an exception to prevent the system from running out of memory.

    Snap Execution

    Default value: Validate & Execute
    Example: Disabled

    Dropdown list

    Select one of the three modes in which the Snap executes. Available options are:

    • Validate & Execute: Performs limited execution of the Snap, and generates a data preview during Pipeline validation. Subsequently, performs full execution of the Snap (unlimited records) during Pipeline runtime.

    • Execute only: Performs full execution of the Snap during Pipeline execution without generating preview data.

    • Disabled: Disables the Snap and all Snaps that are downstream from it.

    ...

    The following example pipeline demonstrates how to concatenate unique string values.

    Snap Configuration

    Output

    Image Removed

    Image Added

    		Image RemovedImage Added

    Count the occurrences of a given product name

    The following example Pipeline shows pipeline demonstrates how to use the Aggregate Snap to count the occurrences of a given product name.

    ...

    Snap Configuration

    ...

    Output

    ...

    Downloads

    Info

    1. Download and import the Pipeline into SnapLogic.

    2. Configure Snap accounts as applicable.

    3. Provide Pipeline parameters as applicable.

    ...