The task isn't that simple as it sounds, as you have to performs several loops in a row to get the desired result.
Here's a script that does the job as you described it.
var aCities = [], // Array for city names
oRows = Table0.resolveNodes("Row1[*]"), // node list of input table rows
oResults, oSources, i, j, oNode, oTest, iSum,
isExisting = function (value) { // compare function
return value === this.testValue;
};
// check every input table row to collect all entered city names
for (i = 0; i < oRows.length; i += 1) {
oNode = oRows.item(i); // create an object ot the current node
oTest = {testValue : oNode.City.rawValue}; // save its city name into a test object
if (!aCities.some(isExisting, oTest)) { // check if the same city doesn't already exists in the array of city names
aCities.push(oNode.City.rawValue); // if not add it to the array
}
}
// add as may rows in the output table as city names found in the input table
Table2._Row1.setInstances(aCities.length);
oResults = Table2.resolveNodes("Row1[*]"); // node list of output table rows
// for every city name calculate the total amount
aCities.forEach(function (cCity, iIndex) {
iSum = 0; // reset the summary
oSources = Table0.resolveNodes('Row1.[City eq "' + cCity + '"]'); // filter all rows in the input table with the current city name
// Build the total amount from the filted nodes
for (j = 0; j < oSources.length; j += 1) { //
iSum += parseInt(oSources.item(j).Amount.rawValue, 10);
}
oResults.item(iIndex).City.rawValue = cCity; // output the city name
oResults.item(iIndex).TotalAmt.rawValue = iSum; // output the total amount
});