Articles in this section

See more

Convert Fields - "aggregate"

Avatar
Tom Sherwood
  • Updated
Follow

Take values for all lines with a matching key, and perform actions according to the rules specified (for example calculate the sum of the values or create layered metrics). Please note that fields to be aggregated have to be numeric. 

Available Functions:
  • sum: 1 + 1 = 2
  • len: counts rows that were processed.
  • avg: calculates the mean average.
  • min: returns minimum value of aggregated column. Works with non-numeric data types as well.
  • max: returns maximum value of aggregated column.
  • json: creates a dictionary with field_to_be_aggregated as an array of [column_as_key, column_as_value].

Example 1

aggregate_example_1.png

Example Input 1
Campaign Ad Group Clicks
Brand media 7
Brand ecommerce 3
Dashboard media 18
Brand ecommerce 4
Dashboard media|social 5
Dashboard media 11
Example Output 1
Campaign Ad Group Sum_Clicks Max_Clicks
Brand ecommerce 7 4
Brand media 7 7
Dashboard media 29 18
Dashboard media|social 5 5

 

Example 2

aggregate_example_2.png

Example Input 2
Campaign Ad Group Sum_Clicks Max_Clicks
Brand ecommerce 7 4
Brand media 3 7
Dashboard media 29 18
Dashboard ecommerce 5 5
Example Output 2
Campaign Dict_Sum Dict_Max
Brand {“ecommerce”:”7”,”media”:”7”} {“ecommerce”:”7”,”media”:”7”}
Dashboard {“media”:”29”,”media|social”:”5”} {“media”:”18”,”media|social”:”5”}

 

I Ain't Afraid of No Code!

Example 1 
[
    [
        "aggregate",
        {
            "subtable": null,
            "presorted": false,
            "rules": [
                [
                    "Sum_Clicks",
                    "Clicks",
                    "sum"
                ],
                [
                    "Max_Clicks",
                    "Clicks",
                    "max"
                ]
            ],
            "key": [
                "Campaign",
                "Ad group"
            ],
            "_comment": null
        }
    ]
]

Example 2

[
    [
        "aggregate",
        {
            "subtable": null,
            "presorted": false,
            "rules": [
                [
                    "Dict_Clicks",
                    [
                        "Ad Group",
                        "Sum_Clicks"
                    ],
                    "json"
                ],
                [
                    "Dict_Max",
                    [
                        "Ad Group",
                        "Max_Clicks"
                    ],
                    "json"
                ]
            ],
            "key": [
                "Campaign"
            ],
            "_comment": null
        }
    ]
]

param str subtable: Name a subtable in which output will be stored (will create subtable where necessary).

param boolean presorted: Default is false.

param list rules: Rules to specify how aggregation should be performed. Possible operators are: sum , len , avg , min , max , json

param list key: Field(s) to be used as a key for aggregation.

param str comment: A comment that will appear in the Transformation UI.

Related articles

Comments

0 comments

Article is closed for comments.