1

I am working on a mileage application where I will need to summarize the miles driven by day and employee. I also need to determine the first trip taken each day by each employee. Here is the sample array that includes the date, employee, miles driven, and beginning odometer reading: 

var records = [
    ["2019-08-26", emp1, 110, 45878],
    ["2019-08-26", emp2, 79, 21561],
    ["2019-09-02", emp1, 111, 46900],
    ["2019-09-02", emp2, 59, 21789],
    ["2019-09-02", emp3, 22, 95781],
    ["2019-09-05", emp3, 26, 95835],
    ["2019-09-02", emp1, 12, 46971],
    ["2019-09-05", emp3, 50, 95895],
    ["2019-09-02", emp2, 49, 21812],
    ["2019-09-05", emp1, 69, 47011],
    ["2019-09-02", emp1, 42, 46950],
    ["2019-09-05", emp3, 21, 95911],
    ["2019-09-02", emp2, 101, 22017],
    ["2019-09-05", emp3, 78, 96001],
    ["2019-09-02", emp3, 35, 95421],
    ["2019-09-05", emp1, 47, 47065]
]

I am trying to create an object with the following output. 

[
  {date: "2019-08-26", employee: emp1, miles: 111, firstTrip: 110},
  {date: "2019-08-26", employee: emp2, miles: 79, firstTrip: 79},
  {date: "2019-09-02", employee: emp1, miles: 65, firstTrip: 111},
  {date: "2019-09-02", employee: emp2, miles: 209, firstTrip: 59},
  {date: "2019-09-02", employee: emp3, miles: 57, firstTrip: 35}, 

etc

]

However, I can't seem to figure out how to identify the first distance traveled by analyzing the lowest odometer reading each day for each employee. Here is the using the reduce function that @Macarthurval helped me out with for everything but the "firstTrip":

var summary = records.reduce( function(total,record){
    var index = -1;
    total.forEach( function(s,i){
        if( s.date == record[0] && s.employee == record[1] ) index = i;
    });
    if( index == -1 ){
        total.push( {date: record[0], employee: record[1], miles: record[2]} );
    }else{
        total[ index ].miles += record[2];
        total[ index ].firstTrip = record[3];
    }
    return total;
},[]);
haby22
  • 77
  • 8

1 Answers1

1

You could take a hash table and a combined key from date and employee and generate a new object of not exists and update miles.

At the end get the values from the object as result set.

var records = [['2019-08-26', 'emp1', 110, 45878], ['2019-08-26', 'emp2', 79, 21561], ['2019-09-02', 'emp1', 111, 46900], ['2019-09-02', 'emp2', 59, 21789], ['2019-09-02', 'emp3', 22, 95781], ['2019-09-05', 'emp3', 26, 95835], ['2019-09-02', 'emp1', 12, 46971], ['2019-09-05', 'emp3', 50, 95895], ['2019-09-02', 'emp2', 49, 21812], ['2019-09-05', 'emp1', 69, 47011], ['2019-09-02', 'emp1', 42, 46950], ['2019-09-05', 'emp3', 21, 95911], ['2019-09-02', 'emp2', 101, 22017], ['2019-09-05', 'emp3', 78, 96001], ['2019-09-02', 'emp3', 35, 95421], ['2019-09-05', 'emp1', 47, 47065]],
    temp = records.reduce(function (r, data) {
        var key = data[0] + '|' + data[1];
        r[key] = r[key] || { payload: { date: data[0], employee: data[1], miles: 0, firstTrip: data[2] }, odo: data[3] };
        r[key].payload.miles += data[2];
        if (data[3] < r[key].odo) {
            r[key].payload.firstTrip = data[2];
            r[key].odo = data[3];
        }
        return r;
    }, {}),
    result = Object.keys(temp).map(function (key) { return temp[key].payload; })

console.log(result);
.as-console-wrapper { max-height: 100% !important; top: 0; }
Nina Scholz
  • 376,160
  • 25
  • 347
  • 392
  • Thanks @Nina Scholz. I'm having trouble creating this on google appscript. I've tried converting the arrow functions but can't get it to work. Also, it looks like for 9/2/19 emp3, the firstTrip should be 35 no? Since the lowest odo reading for that day is 95421. Appreciate your help. – haby22 Jan 02 '20 at 20:33
  • please see edit, regarding the first trip. which javascript version is *google appscript*? – Nina Scholz Jan 02 '20 at 20:42
  • Hi Nina, it looks like it's 1.8 – haby22 Jan 02 '20 at 20:52
  • what is 1.8? do you have a source with the features? – Nina Scholz Jan 02 '20 at 20:55
  • Here is some references:https://stackoverflow.com/questions/17252409/which-edition-of-ecma-262-does-google-apps-script-support – haby22 Jan 02 '20 at 21:13