How to group array or column based data by a specific item's/column's value while summing-up the values of another?

Question

We are building an integration in a SAAS product which exports data from a table and writes it to a CSV file. Tool allows JavaScript (Which i am not very familiar with) code to be written which can read data from table and write it to CSV file and transport file to a S3 bucket.

I am able to get to work till this point. Sample JavaScript -

// Manually invoke this method via 'Test connection'
function testConnection(context) {
  return true;
}
// Manually invoke this method via 'Run manually'
function exportData(context) {

  // Write the message to the log.
  ai.log.logInfo("Script Execution Starting...");
  var dataSource = context.getDataSource();

  var bucketName = dataSource.getSetting("Bucket Name").getValue();
  var awsAccessKey = dataSource.getSetting("Access Key").getValue();
  var awsSecretKey = dataSource.getSetting("Access Secret Key").getValue();
  var region = dataSource.getSetting("Region").getValue();
  var date = new Date();
  var currentDate = date.toLocaleDateString().replaceAll("/","");
  //Append unique number and current date to the file name.
  var key = dataSource.getSetting("Output File Name").getValue() + "_" + Date.now() + "_" + currentDate + ".csv";
  ai.log.logInfo("Preparing Output File : " + key);

  // Step 1: Build an array with the data that needs to be written to the csv file.
  var reader = context.createTableReader(['SqlProjectCodeOnly', 'ResCodeCategory', 'Period', 'SqlAWSAccountID', 'SqlAWSAccountName', 'SqlAWSAccountRegion', 'Value']);
  // Step 2: Write the header of the output file.
  var result = "Project|Product Name|Report Month|AWS Account ID|AWS Account Name|AWS Region|Adjusted Cost\n";
  var row = null;
  // If reader.readRow() returns null, then there are no more records.
  while ((row = reader.readRow()) !== null) {
    //Separate each field by pipe.
    result += row.join("|") + '\n';
  }
  ai.awss3.putFile(bucketName, key, result, region, awsAccessKey, awsSecretKey);
  ai.log.logInfo("Script Execution Completed.");
}

It outputs the data in the following format -

Project|Product Name|Report Month|AWS Account ID|AWS Account Name|AWS Region|Adjusted Cost

XXXXXXX|AWS Elastic Compute Cloud|01/2022|AWS Account ID (Uncategorized)|AWS Account Name (Uncategorized)|AWS Region (Uncategorized)|2588.598833

XXXXXXX|AWS Elastic Compute Cloud|01/2022|AWS Account ID (Uncategorized)|AWS Account Name (Uncategorized)|AWS Region (Uncategorized)|1553.1593004

XXXXXXX|AWS Elastic Compute Cloud|02/2022|AWS Account ID (Uncategorized)|AWS Account Name (Uncategorized)|AWS Region (Uncategorized)|4141.7581328

XXXXXXX|AWS Elastic Compute Cloud|02/2022|AWS Account ID (Uncategorized)|AWS Account Name (Uncategorized)|AWS Region (Uncategorized)|1553.1593004

Now it need to group the data by first six columns and sum the value of last column to have data in the below format -

Project|Product Name|Report Month|AWS Account ID|AWS Account Name|AWS Region|Adjusted Cost
XXXXXXX|AWS Elastic Compute Cloud|01/2022|AWS Account ID (Uncategorized)|AWS Account Name (Uncategorized)|AWS Region (Uncategorized)|4,141.7581334
XXXXXXX|AWS Elastic Compute Cloud|02/2022|AWS Account ID (Uncategorized)|AWS Account Name (Uncategorized)|AWS Region (Uncategorized)|5,694.9174338

I tried using the reduce function, but not able to get it to work. When i write below to the log -

ai.log.logVerbose( " row: " + row);

It writes data like the following -

row: XXXXXXX|AWS Elastic Compute Cloud|01/2022|AWS Account ID (Uncategorized)|AWS Account Name (Uncategorized)|AWS Region (Uncategorized)|2588.598833

row: XXXXXXX|AWS Elastic Compute Cloud|01/2022|AWS Account ID (Uncategorized)|AWS Account Name (Uncategorized)|AWS Region (Uncategorized)|1553.1593004

row: XXXXXXX|AWS Elastic Compute Cloud|02/2022|AWS Account ID (Uncategorized)|AWS Account Name (Uncategorized)|AWS Region (Uncategorized)|4141.7581328

row: XXXXXXX|AWS Elastic Compute Cloud|02/2022|AWS Account ID (Uncategorized)|AWS Account Name (Uncategorized)|AWS Region (Uncategorized)|1553.1593004.

Would really appreciate if experts out there can help with this.

Answer 1

While reading each row, one has to push it into an e.g. rows array. This array then can be reduceed into an object which holds groups of aggregated columns which are grouped by the same report month. Such a groups object's values then can be mapped and joined into the final result.

function collectAndAggregateSameReportMonthAdjustedCost(groups, recordColumns) {
  const reportMonth = recordColumns[2];
  const adjustedCost = parseFloat(recordColumns[6]);

  let groupedColumns = groups[reportMonth];
  if (!groupedColumns) {

    groupedColumns = groups[reportMonth] = [...recordColumns];
    groupedColumns[6] = adjustedCost;
  } else {
    groupedColumns[6] = groupedColumns[6] + adjustedCost;
  }
  return groups;
}

// const rows = [];
let row;

// // If reader.readRow() returns null, then there are no more records.
// while ((row = reader.readRow()) !== null) {
//   rows.push(row);
// }

// `rows` after being aggregated by the above commented code
// will loke like the next provided demonstrator ...
const rows = [[
  'XXXXXXX',
  'AWS Elastic Compute Cloud',
  '01/2022',
  'AWS Account ID (Uncategorized)',
  'AWS Account Name (Uncategorized)',
  'AWS Region (Uncategorized)',
  '2588.598833',
], [
  'XXXXXXX',
  'AWS Elastic Compute Cloud',
  '01/2022',
  'AWS Account ID (Uncategorized)',
  'AWS Account Name (Uncategorized)',
  'AWS Region (Uncategorized)',
  '1553.1593004',
], [
  'XXXXXXX',
  'AWS Elastic Compute Cloud',
  '02/2022',
  'AWS Account ID (Uncategorized)',
  'AWS Account Name (Uncategorized)',
  'AWS Region (Uncategorized)',
  '4141.7581328',
], [
  'XXXXXXX',
  'AWS Elastic Compute Cloud',
  '02/2022',
  'AWS Account ID (Uncategorized)',
  'AWS Account Name (Uncategorized)',
  'AWS Region (Uncategorized)',
  '1553.1593004',
]];

const aggregatedRows = Object
  .values(
    rows
      .reduce(collectAndAggregateSameReportMonthAdjustedCost, {})
  );
console.log({ aggregatedRows });

const result = [

  'Project|Product Name|Report Month|AWS Account ID|AWS Account Name|AWS Region|Adjusted Cost',
  ...aggregatedRows
    .map(columns => {
      columns[6] = columns[6]
        .toLocaleString('en', {
          useGrouping: 'always',
          maximumFractionDigits: 7,
        });
      return columns.join('|');
    }),

].join('\n');

console.log(result);

.as-console-wrapper { min-height: 100%!important; top: 0; }