Skip to content

How to sum a array like a Excel Pivot where two keys must match?

I try to sum all “Menge” and “Fehler” values where “Datum” AND “Material” must match. The result should look like an Excel Pivot.

This is my code so far, but I don’t know how to add the second key “Material” that also must match . I hope you can understand what I try to explain.

var arr = [{
  "Datum": {
    "date": "2000-01-01 00:00:00.000000",
    "timezone_type": 3,
    "timezone": "Europe/Berlin"
  },
  "Material": "123",
  "Menge": 100,
  "Fehler": 5
}, {
  "Datum": {
    "date": "2000-01-01 00:00:00.000000",
    "timezone_type": 3,
    "timezone": "Europe/Berlin"
  },
  "Material": "123",
  "Menge": 5,
  "Fehler": 1
}, {
  "Datum": {
    "date": "2000-01-01 00:00:00.000000",
    "timezone_type": 3,
    "timezone": "Europe/Berlin"
  },
  "Material": "123",
  "Menge": 6,
  "Fehler": 65
}, {
  "Datum": {
    "date": "2000-01-01 00:00:00.000000",
    "timezone_type": 3,
    "timezone": "Europe/Berlin"
  },
  "Material": "222",
  "Menge": 10,
  "Fehler": 5
}, {
  "Datum": {
    "date": "2000-01-02 00:00:00.000000",
    "timezone_type": 3,
    "timezone": "Europe/Berlin"
  },
  "Material": "444",
  "Menge": 29,
  "Fehler": 1
}, {
  "Datum": {
    "date": "2000-01-02 00:00:00.000000",
    "timezone_type": 3,
    "timezone": "Europe/Berlin"
  },
  "Material": "123",
  "Menge": 1,
  "Fehler": 1
}]

const result = Object.values(arr.reduce((acc, obj) => {
  const [Datum] = obj.Datum.date.split(' ');
  const Menge = (acc[Datum] ?.Menge + obj.Menge) || obj.Menge;
  const Fehler = (acc[Datum] ?.Fehler + obj.Fehler) || obj.Fehler;
  acc[Datum] = {
    Datum,
    Menge,
    Fehler
  };
  return acc;
}, {}));

console.log(result)

But the result should look like:

[{
    "Datum": "2000-01-01",
    "Material": "123",
    "Menge": 111,
    "Fehler": 71
  },
  {
    "Datum": "2000-01-01",
    "Material": "222",
    "Menge": 10,
    "Fehler": 5
  },
  {
    "Datum": "2000-01-02",
    "Material": "444",
    "Menge": 29,
    "Fehler": 1
  },
  {
    "Datum": "2000-01-02",
    "Material": "123",
    "Menge": 1,
    "Fehler": 1
  }
]

Thank you very much

Answer

You need a combined key with Datum and Material.

const
    data = [{ Datum: { date: "2000-01-01 00:00:00.000000", timezone_type: 3, timezone: "Europe/Berlin" }, Material: "123", Menge: 100, Fehler: 5 }, { Datum: { date: "2000-01-01 00:00:00.000000", timezone_type: 3, timezone: "Europe/Berlin" }, Material: "123", Menge: 5, Fehler: 1 }, { Datum: { date: "2000-01-01 00:00:00.000000", timezone_type: 3, timezone: "Europe/Berlin" }, Material: "123", Menge: 6, Fehler: 65 }, { Datum: { date: "2000-01-01 00:00:00.000000", timezone_type: 3, timezone: "Europe/Berlin" }, Material: "222", Menge: 10, Fehler: 5 }, { Datum: { date: "2000-01-02 00:00:00.000000", timezone_type: 3, timezone: "Europe/Berlin" }, Material: "444", Menge: 29, Fehler: 1 }, { Datum: { date: "2000-01-02 00:00:00.000000", timezone_type: 3, timezone: "Europe/Berlin" }, Material: "123", Menge: 1, Fehler: 1 }],
    result = Object.values(data.reduce((acc, { Datum: { date }, Material, Menge, Fehler }) => {
        const
            Datum = date.slice(0, 10),
            key = [Datum, Material].join('|');

        acc[key] ??= { Datum, Material, Menge: 0, Fehler: 0 };
        acc[key].Menge += Menge,
        acc[key].Fehler += Fehler;

        return acc;
    }, {}));

console.log(result);
.as-console-wrapper { max-height: 100% !important; top: 0; }