Group by one field and sum other in Function

I would appreciate assistance with a custom function, I have no idea where to start.

Describe the issue/error/question

I have Call Data Records (CDR’s) that I want to group by CLI (Extension) and then sum the charged_amount to give me a list with the groups Total charge and the CDRs as a arry below it.

Below I have put example Last Node Data and expected data, this is just 5 records, some results have 1000 and many CLI’s not just these two.

What is the error message (if any)?

No error, help needed with data transformation

Please share the workflow


## Share the output returned by the last node
Last node output (Item list)
[
   {
      "destination": "27627",
      "i_dest": 62188,
      "CLD": "27627000000",
      "i_xdr": 53627137,
      "beneficiary_account_id": "1118100",
      "i_account": 19707,
      "connect_time": "2022-05-10 07:19:31",
      "charged_amount": 0.08904,
      "bit_flags": 4,
      "unix_connect_time": 1652167171,
      "history": "[email protected]",
      "xdr_type": "account",
      "bill_status": "O",
      "call_id": "[email protected]",
      "service": "Voice Calls",
      "description": "CellC and Telkom Mobile",
      "account_id": "1118100",
      "aux_xdrs": [
         {
            "value": "201",
            "xdr_time": "2022-05-10 07:19:35",
            "xdr_column_id": "xcli"
         },
         {
            "value": "4",
            "xdr_time": "2022-05-10 07:19:35",
            "xdr_column_id": "media_duration"
         }
      ],
      "setup_time": 2779,
      "h323_conf_id": "A74F2D51 26D2B70F 9FE1ADBB 6BFCD89F",
      "disconnect_time": "2022-05-10 07:19:35",
      "disconnect_reason": "Normal call clearing",
      "i_service": 3,
      "subdivision": "",
      "iso_3166_1_a3": "ZAF",
      "CLI": "201 (Office Manager)",
      "bill_time": "2022-05-10 07:19:31",
      "i_rate": 1018461,
      "is_split": "0",
      "country": "South Africa",
      "unix_disconnect_time": 1652167175,
      "disconnect_cause": "16",
      "charged_quantity": 4,
      "i_customer": 1033
   },
   {
      "destination": "2784",
      "i_dest": 62192,
      "CLD": "27848000000",
      "i_xdr": 53025668,
      "beneficiary_account_id": "1118100",
      "i_account": 19707,
      "connect_time": "2022-04-25 07:31:45",
      "charged_amount": 1.0017,
      "bit_flags": 4,
      "unix_connect_time": 1650871905,
      "history": "[email protected]",
      "xdr_type": "account",
      "bill_status": "O",
      "call_id": "[email protected]",
      "service": "Voice Calls",
      "description": "CellC and Telkom Mobile",
      "account_id": "1118100",
      "aux_xdrs": [
         {
            "value": "201",
            "xdr_time": "2022-04-25 07:32:29",
            "xdr_column_id": "xcli"
         },
         {
            "value": "44",
            "xdr_time": "2022-04-25 07:32:29",
            "xdr_column_id": "media_duration"
         }
      ],
      "setup_time": 1491,
      "h323_conf_id": "0CD5C2F7 8D1B65BF 3EB92296 5268D10B",
      "disconnect_time": "2022-04-25 07:32:29",
      "disconnect_reason": "Normal call clearing",
      "i_service": 3,
      "subdivision": "",
      "iso_3166_1_a3": "ZAF",
      "CLI": "201 (Office Manager)",
      "bill_time": "2022-04-25 07:31:45",
      "i_rate": 1018594,
      "is_split": "0",
      "country": "South Africa",
      "unix_disconnect_time": 1650871949,
      "disconnect_cause": "16",
      "charged_quantity": 45,
      "i_customer": 1033
   },
   {
      "destination": "2782",
      "i_dest": 63333,
      "CLD": "27827000000",
      "i_xdr": 54128773,
      "beneficiary_account_id": "1118100",
      "i_account": 19707,
      "connect_time": "2022-05-20 12:28:30",
      "charged_amount": 0.6678,
      "bit_flags": 4,
      "unix_connect_time": 1653049710,
      "history": "[email protected]",
      "xdr_type": "account",
      "bill_status": "O",
      "call_id": "[email protected]",
      "service": "Voice Calls",
      "description": "VOIP Provider",
      "account_id": "1118100",
      "aux_xdrs": [
         {
            "value": "201",
            "xdr_time": "2022-05-20 12:29:12",
            "xdr_column_id": "xcli"
         },
         {
            "value": "41",
            "xdr_time": "2022-05-20 12:29:12",
            "xdr_column_id": "media_duration"
         }
      ],
      "setup_time": 1008,
      "h323_conf_id": "230AB410 EF5BAFBE 94DAA9F3 B9DE8604",
      "disconnect_time": "2022-05-20 12:29:12",
      "disconnect_reason": "Normal call clearing",
      "i_service": 3,
      "subdivision": "",
      "iso_3166_1_a3": "ZAF",
      "CLI": "201 (Office Manager)",
      "bill_time": "2022-05-20 12:28:30",
      "i_rate": 1018592,
      "is_split": "0",
      "country": "South Africa",
      "unix_disconnect_time": 1653049752,
      "disconnect_cause": "16",
      "charged_quantity": 42,
      "i_customer": 1033
   },
   {
      "destination": "27603",
      "i_dest": 62437,
      "CLD": "27603000000",
      "i_xdr": 54125153,
      "beneficiary_account_id": "1118101",
      "i_account": 19708,
      "connect_time": "2022-05-20 11:37:29",
      "charged_amount": 6.7734,
      "bit_flags": 4,
      "unix_connect_time": 1653046649,
      "history": "[email protected]",
      "xdr_type": "account",
      "bill_status": "O",
      "call_id": "[email protected]",
      "service": "Voice Calls",
      "description": "Vodacom & MTN",
      "account_id": "1118101",
      "aux_xdrs": [
         {
            "value": "202",
            "xdr_time": "2022-05-20 11:44:35",
            "xdr_column_id": "xcli"
         },
         {
            "value": "426",
            "xdr_time": "2022-05-20 11:44:35",
            "xdr_column_id": "media_duration"
         }
      ],
      "setup_time": 2306,
      "h323_conf_id": "11275F5F 275933AD A490D460 AF927DAA",
      "disconnect_time": "2022-05-20 11:44:35",
      "disconnect_reason": "Normal call clearing",
      "i_service": 3,
      "subdivision": "",
      "iso_3166_1_a3": "ZAF",
      "CLI": "202 (Natalia)",
      "bill_time": "2022-05-20 11:37:29",
      "i_rate": 1018437,
      "is_split": "0",
      "country": "South Africa",
      "unix_disconnect_time": 1653047075,
      "disconnect_cause": "16",
      "charged_quantity": 426,
      "i_customer": 1033
   },
   {
      "destination": "2774",
      "i_dest": 62191,
      "CLD": "27742000000",
      "i_xdr": 53883808,
      "beneficiary_account_id": "1118101",
      "i_account": 19708,
      "connect_time": "2022-05-16 10:24:57",
      "charged_amount": 15.98268,
      "bit_flags": 4,
      "unix_connect_time": 1652696697,
      "history": "[email protected]",
      "xdr_type": "account",
      "bill_status": "O",
      "call_id": "[email protected]",
      "service": "Voice Calls",
      "description": "CellC and Telkom Mobile",
      "account_id": "1118101",
      "aux_xdrs": [
         {
            "value": "202",
            "xdr_time": "2022-05-16 10:36:55",
            "xdr_column_id": "xcli"
         },
         {
            "value": "718",
            "xdr_time": "2022-05-16 10:36:55",
            "xdr_column_id": "media_duration"
         }
      ],
      "setup_time": 1169,
      "h323_conf_id": "728C3C7F 8AAA87C8 1AC22ED4 A222AA8C",
      "disconnect_time": "2022-05-16 10:36:55",
      "disconnect_reason": "Normal call clearing",
      "i_service": 3,
      "subdivision": "",
      "iso_3166_1_a3": "ZAF",
      "CLI": "202 (Natalia)",
      "bill_time": "2022-05-16 10:24:57",
      "i_rate": 1018487,
      "is_split": "0",
      "country": "South Africa",
      "unix_disconnect_time": 1652697415,
      "disconnect_cause": "16",
      "charged_quantity": 718,
      "i_customer": 1033
   }
]


Expected output

[
  {
    "CLI": "201 (Office Manager",
    "TOTAL_CHARGED": 1.75854,
    "cdrs": [
      {
        "destination": "27627",
        "i_dest": 62188,
        "CLD": "27627932067",
        "i_xdr": 53627137,
        "beneficiary_account_id": "1118100",
        "i_account": 19707,
        "connect_time": "2022-05-10 07:19:31",
        "charged_amount": 0.08904,
        "bit_flags": 4,
        "unix_connect_time": 1652167171,
        "history": "[email protected]",
        "xdr_type": "account",
        "bill_status": "O",
        "call_id": "[email protected]",
        "service": "Voice Calls",
        "description": "CellC and Telkom Mobile",
        "account_id": "1118100",
        "aux_xdrs": [
          {
            "value": "201",
            "xdr_time": "2022-05-10 07:19:35",
            "xdr_column_id": "xcli"
          },
          {
            "value": "4",
            "xdr_time": "2022-05-10 07:19:35",
            "xdr_column_id": "media_duration"
          }
        ],
        "setup_time": 2779,
        "h323_conf_id": "A74F2D51 26D2B70F 9FE1ADBB 6BFCD89F",
        "disconnect_time": "2022-05-10 07:19:35",
        "disconnect_reason": "Normal call clearing",
        "i_service": 3,
        "subdivision": "",
        "iso_3166_1_a3": "ZAF",
        "CLI": "201 (Office Manager)",
        "bill_time": "2022-05-10 07:19:31",
        "i_rate": 1018461,
        "is_split": "0",
        "country": "South Africa",
        "unix_disconnect_time": 1652167175,
        "disconnect_cause": "16",
        "charged_quantity": 4,
        "i_customer": 1033
      },
      {
        "destination": "2784",
        "i_dest": 62192,
        "CLD": "27848333555",
        "i_xdr": 53025668,
        "beneficiary_account_id": "1118100",
        "i_account": 19707,
        "connect_time": "2022-04-25 07:31:45",
        "charged_amount": 1.0017,
        "bit_flags": 4,
        "unix_connect_time": 1650871905,
        "history": "[email protected]",
        "xdr_type": "account",
        "bill_status": "O",
        "call_id": "[email protected]",
        "service": "Voice Calls",
        "description": "CellC and Telkom Mobile",
        "account_id": "1118100",
        "aux_xdrs": [
          {
            "value": "201",
            "xdr_time": "2022-04-25 07:32:29",
            "xdr_column_id": "xcli"
          },
          {
            "value": "44",
            "xdr_time": "2022-04-25 07:32:29",
            "xdr_column_id": "media_duration"
          }
        ],
        "setup_time": 1491,
        "h323_conf_id": "0CD5C2F7 8D1B65BF 3EB92296 5268D10B",
        "disconnect_time": "2022-04-25 07:32:29",
        "disconnect_reason": "Normal call clearing",
        "i_service": 3,
        "subdivision": "",
        "iso_3166_1_a3": "ZAF",
        "CLI": "201 (Office Manager)",
        "bill_time": "2022-04-25 07:31:45",
        "i_rate": 1018594,
        "is_split": "0",
        "country": "South Africa",
        "unix_disconnect_time": 1650871949,
        "disconnect_cause": "16",
        "charged_quantity": 45,
        "i_customer": 1033
      },
      {
        "destination": "2782",
        "i_dest": 63333,
        "CLD": "27827192823",
        "i_xdr": 54128773,
        "beneficiary_account_id": "1118100",
        "i_account": 19707,
        "connect_time": "2022-05-20 12:28:30",
        "charged_amount": 0.6678,
        "bit_flags": 4,
        "unix_connect_time": 1653049710,
        "history": "[email protected]",
        "xdr_type": "account",
        "bill_status": "O",
        "call_id": "[email protected]",
        "service": "Voice Calls",
        "description": "VOIP Provider",
        "account_id": "1118100",
        "aux_xdrs": [
          {
            "value": "201",
            "xdr_time": "2022-05-20 12:29:12",
            "xdr_column_id": "xcli"
          },
          {
            "value": "41",
            "xdr_time": "2022-05-20 12:29:12",
            "xdr_column_id": "media_duration"
          }
        ],
        "setup_time": 1008,
        "h323_conf_id": "230AB410 EF5BAFBE 94DAA9F3 B9DE8604",
        "disconnect_time": "2022-05-20 12:29:12",
        "disconnect_reason": "Normal call clearing",
        "i_service": 3,
        "subdivision": "",
        "iso_3166_1_a3": "ZAF",
        "CLI": "201 (Office Manager)",
        "bill_time": "2022-05-20 12:28:30",
        "i_rate": 1018592,
        "is_split": "0",
        "country": "South Africa",
        "unix_disconnect_time": 1653049752,
        "disconnect_cause": "16",
        "charged_quantity": 42,
        "i_customer": 1033
      }
    ]
  },
  {
    "CLI": "202 (Natalia)",
    "TOTAL_CHARGED": 22.75608,
    "cdrs": [
      {
        "destination": "27603",
        "i_dest": 62437,
        "CLD": "27603004395",
        "i_xdr": 54125153,
        "beneficiary_account_id": "1118101",
        "i_account": 19708,
        "connect_time": "2022-05-20 11:37:29",
        "charged_amount": 6.7734,
        "bit_flags": 4,
        "unix_connect_time": 1653046649,
        "history": "[email protected]",
        "xdr_type": "account",
        "bill_status": "O",
        "call_id": "[email protected]",
        "service": "Voice Calls",
        "description": "Vodacom & MTN",
        "account_id": "1118101",
        "aux_xdrs": [
          {
            "value": "202",
            "xdr_time": "2022-05-20 11:44:35",
            "xdr_column_id": "xcli"
          },
          {
            "value": "426",
            "xdr_time": "2022-05-20 11:44:35",
            "xdr_column_id": "media_duration"
          }
        ],
        "setup_time": 2306,
        "h323_conf_id": "11275F5F 275933AD A490D460 AF927DAA",
        "disconnect_time": "2022-05-20 11:44:35",
        "disconnect_reason": "Normal call clearing",
        "i_service": 3,
        "subdivision": "",
        "iso_3166_1_a3": "ZAF",
        "CLI": "202 (Natalia)",
        "bill_time": "2022-05-20 11:37:29",
        "i_rate": 1018437,
        "is_split": "0",
        "country": "South Africa",
        "unix_disconnect_time": 1653047075,
        "disconnect_cause": "16",
        "charged_quantity": 426,
        "i_customer": 1033
      },
      {
        "destination": "2774",
        "i_dest": 62191,
        "CLD": "27742507298",
        "i_xdr": 53883808,
        "beneficiary_account_id": "1118101",
        "i_account": 19708,
        "connect_time": "2022-05-16 10:24:57",
        "charged_amount": 15.98268,
        "bit_flags": 4,
        "unix_connect_time": 1652696697,
        "history": "[email protected]",
        "xdr_type": "account",
        "bill_status": "O",
        "call_id": "[email protected]",
        "service": "Voice Calls",
        "description": "CellC and Telkom Mobile",
        "account_id": "1118101",
        "aux_xdrs": [
          {
            "value": "202",
            "xdr_time": "2022-05-16 10:36:55",
            "xdr_column_id": "xcli"
          },
          {
            "value": "718",
            "xdr_time": "2022-05-16 10:36:55",
            "xdr_column_id": "media_duration"
          }
        ],
        "setup_time": 1169,
        "h323_conf_id": "728C3C7F 8AAA87C8 1AC22ED4 A222AA8C",
        "disconnect_time": "2022-05-16 10:36:55",
        "disconnect_reason": "Normal call clearing",
        "i_service": 3,
        "subdivision": "",
        "iso_3166_1_a3": "ZAF",
        "CLI": "202 (Natalia)",
        "bill_time": "2022-05-16 10:24:57",
        "i_rate": 1018487,
        "is_split": "0",
        "country": "South Africa",
        "unix_disconnect_time": 1652697415,
        "disconnect_cause": "16",
        "charged_quantity": 718,
        "i_customer": 1033
      }
    ]
  }
]

Hey @mvandyk, I am afraid the transformation you have in mind would require a bit of code. Based on this approach I used earlier, I’d suggest something like this:

This would add a new includedItems and total field for each of your CLI values which you can of course rename as needed:

Thank you, I will test, really appreciate feedback, I love N8N but when it comes to coding im useless :slight_smile:

OK tested with large volume data. Works better than I could have asked for. Appreciate assistance greatly.

1 Like

Sweet, glad to hear this works for you!