How to Reduce array with group by

lock 0
Viewed 32

I have a record, i am trying to group by the data w.r.t COAGrpCode,ldgrGrp,. Means I have to sum Opening, PrdDr ,PrdCr and Closing w.r.t COAGrpCode,ldgrGrp.

Below is the data. It has more than 50k records but i am only pasting some of it.

[
    {
        "siteCode": "ANKL",
        "ldgrGrp": "G00",
        "coaGrpId": 355,
        "coaPrntGrpId": 0,
        "coaGrpNm": "Current Liabilities",
        "coaGrpCode": "CL",
        "coaLedger": "Current Liabilities",
        "prntSeq": 3,
        "pL_Item": 0,
        "opening": -4851148.45,
        "prdDr": 0,
        "prdCr": 0,
        "closing": -4851148.45
    },
    {
        "siteCode": "ANKL",
        "ldgrGrp": "G01",
        "coaGrpId": 59,
        "coaPrntGrpId": 355,
        "coaGrpNm": "Duties & Taxes",
        "coaGrpCode": "CLDTX",
        "coaLedger": "Duties & Taxes",
        "prntSeq": 3,
        "pL_Item": 0,
        "opening": -4839648.45,
        "prdDr": 0,
        "prdCr": 0,
        "closing": -4839648.45
    },
    {
        "siteCode": "ANKL",
        "ldgrGrp": "G02",
        "coaGrpId": 55,
        "coaPrntGrpId": 59,
        "coaGrpNm": "Goods & Service Tax",
        "coaGrpCode": "CLDTX05",
        "coaLedger": "Goods & Service Tax",
        "prntSeq": 3,
        "pL_Item": 0,
        "opening": -4839648.45,
        "prdDr": 0,
        "prdCr": 0,
        "closing": -4839648.45
    },
    {
        "siteCode": "ANKL",
        "ldgrGrp": "G03",
        "coaGrpId": 185,
        "coaPrntGrpId": 55,
        "coaGrpNm": "Electronic  Cash Ledger Gst",
        "coaGrpCode": "CLDTX0502",
        "coaLedger": "Electronic  Cash Ledger Gst",
        "prntSeq": 3,
        "pL_Item": 0,
        "opening": 36691,
        "prdDr": 0,
        "prdCr": 0,
        "closing": 36691
    },
    {
        "siteCode": "ANKL",
        "ldgrGrp": "L",
        "coaGrpId": 185,
        "coaPrntGrpId": 185,
        "coaGrpNm": "Electronic  Cash Ledger Gst",
        "coaGrpCode": "CLDTX050200001",
        "coaLedger": "Sgst - Cash Ledger",
        "prntSeq": 3,
        "pL_Item": 0,
        "opening": 34909,
        "prdDr": 0,
        "prdCr": 0,
        "closing": 34909
    },
    {
        "siteCode": "ANKL",
        "ldgrGrp": "L",
        "coaGrpId": 185,
        "coaPrntGrpId": 185,
        "coaGrpNm": "Electronic  Cash Ledger Gst",
        "coaGrpCode": "CLDTX050200002",
        "coaLedger": "Cgst - Cash Ledger",
        "prntSeq": 3,
        "pL_Item": 0,
        "opening": 1536,
        "prdDr": 0,
        "prdCr": 0,
        "closing": 1536
    },
    {
        "siteCode": "ANKL",
        "ldgrGrp": "L",
        "coaGrpId": 185,
        "coaPrntGrpId": 185,
        "coaGrpNm": "Electronic  Cash Ledger Gst",
        "coaGrpCode": "CLDTX050200006",
        "coaLedger": "Igst - Cash Ledger",
        "prntSeq": 3,
        "pL_Item": 0,
        "opening": 246,
        "prdDr": 0,
        "prdCr": 0,
        "closing": 246
    },
    {
        "siteCode": "ANKL",
        "ldgrGrp": "G03",
        "coaGrpId": 66,
        "coaPrntGrpId": 55,
        "coaGrpNm": "Input Gst",
        "coaGrpCode": "CLDTX0503",
        "coaLedger": "Input Gst",
        "prntSeq": 3,
        "pL_Item": 0,
        "opening": 9994907.07,
        "prdDr": 0,
        "prdCr": 0,
        "closing": 9994907.07
    },
    {
        "siteCode": "ANKL",
        "ldgrGrp": "L",
        "coaGrpId": 66,
        "coaPrntGrpId": 66,
        "coaGrpNm": "Input Gst",
        "coaGrpCode": "CLDTX050300004",
        "coaLedger": "Input Igst",
        "prntSeq": 3,
        "pL_Item": 0,
        "opening": 2116005.13,
        "prdDr": 0,
        "prdCr": 0,
        "closing": 2116005.13
    },
    {
        "siteCode": "ANKL",
        "ldgrGrp": "L",
        "coaGrpId": 66,
        "coaPrntGrpId": 66,
        "coaGrpNm": "Input Gst",
        "coaGrpCode": "CLDTX050300006",
        "coaLedger": "Input Cgst",
        "prntSeq": 3,
        "pL_Item": 0,
        "opening": 3939450.61,
        "prdDr": 0,
        "prdCr": 0,
        "closing": 3939450.61
    },
    {
        "siteCode": "ANKL",
        "ldgrGrp": "L",
        "coaGrpId": 66,
        "coaPrntGrpId": 66,
        "coaGrpNm": "Input Gst",
        "coaGrpCode": "CLDTX050300008",
        "coaLedger": "Input Sgst",
        "prntSeq": 3,
        "pL_Item": 0,
        "opening": 3939451.33,
        "prdDr": 0,
        "prdCr": 0,
        "closing": 3939451.33
    }
]

I require output in below format

[
    {
       
        "ldgrGrp": "G00",
        "coaGrpCode": "CL",
        "coaLedger": "Current Liabilities",       
        "opening": Sum of Opening,
        "prdDr": Sum of prdDr,
        "prdCr": Sum of prdCr,
        "closing": Sum of Closing
    },
    {
       
        "ldgrGrp": "G00",
        "coaGrpCode": "CL1",
        "coaLedger": "Current Liabilities1",       
        "opening": Sum of Opening,
        "prdDr": Sum of prdDr,
        "prdCr": Sum of prdCr,
        "closing": Sum of Closing
    }
]

I have tried below code but it is giving sum of opening only. Here Tridata is a usesate where i have stored my raw data.

function filterData() {               
        var r = Tridata.reduce(function (pv, cv) {
            if (pv[cv.coaGrpCode]) {
                pv[cv.coaGrpCode] += cv.opening;
            } else {
                pv[cv.coaGrpCode] = cv.opening;
                
            }
            return pv;
        }, []);

        console.log(r)
    }
ecmascript-6 reactjs arrays javascript
1 Answers

One insight is that you should combine the three key fields into a single string and use that as key in pv. You can use JSON.stringify([cv.ldgrGrp, cv.coaGrpCode, cv.coaLedger]); for that.

With a bit of renaming of variables (pv and cv are a bit cryptic), it would look like this:

const Tridata = [{"siteCode": "ANKL","ldgrGrp": "G00","coaGrpId": 355,"coaPrntGrpId": 0,"coaGrpNm": "Current Liabilities","coaGrpCode": "CL","coaLedger": "Current Liabilities","prntSeq": 3,"pL_Item": 0,"opening": -4851148.45,"prdDr": 0,"prdCr": 0,"closing": -4851148.45},{"siteCode": "ANKL","ldgrGrp": "G01","coaGrpId": 59,"coaPrntGrpId": 355,"coaGrpNm": "Duties & Taxes","coaGrpCode": "CLDTX","coaLedger": "Duties & Taxes","prntSeq": 3,"pL_Item": 0,"opening": -4839648.45,"prdDr": 0,"prdCr": 0,"closing": -4839648.45},{"siteCode": "ANKL","ldgrGrp": "G02","coaGrpId": 55,"coaPrntGrpId": 59,"coaGrpNm": "Goods & Service Tax","coaGrpCode": "CLDTX05","coaLedger": "Goods & Service Tax","prntSeq": 3,"pL_Item": 0,"opening": -4839648.45,"prdDr": 0,"prdCr": 0,"closing": -4839648.45},{"siteCode": "ANKL","ldgrGrp": "G03","coaGrpId": 185,"coaPrntGrpId": 55,"coaGrpNm": "Electronic  Cash Ledger Gst","coaGrpCode": "CLDTX0502","coaLedger": "Electronic  Cash Ledger Gst","prntSeq": 3,"pL_Item": 0,"opening": 36691,"prdDr": 0,"prdCr": 0,"closing": 36691},{"siteCode": "ANKL","ldgrGrp": "L","coaGrpId": 185,"coaPrntGrpId": 185,"coaGrpNm": "Electronic  Cash Ledger Gst","coaGrpCode": "CLDTX050200001","coaLedger": "Sgst - Cash Ledger","prntSeq": 3,"pL_Item": 0,"opening": 34909,"prdDr": 0,"prdCr": 0,"closing": 34909},{"siteCode": "ANKL","ldgrGrp": "L","coaGrpId": 185,"coaPrntGrpId": 185,"coaGrpNm": "Electronic  Cash Ledger Gst","coaGrpCode": "CLDTX050200002","coaLedger": "Cgst - Cash Ledger","prntSeq": 3,"pL_Item": 0,"opening": 1536,"prdDr": 0,"prdCr": 0,"closing": 1536},{"siteCode": "ANKL","ldgrGrp": "L","coaGrpId": 185,"coaPrntGrpId": 185,"coaGrpNm": "Electronic  Cash Ledger Gst","coaGrpCode": "CLDTX050200006","coaLedger": "Igst - Cash Ledger","prntSeq": 3,"pL_Item": 0,"opening": 246,"prdDr": 0,"prdCr": 0,"closing": 246},{"siteCode": "ANKL","ldgrGrp": "G03","coaGrpId": 66,"coaPrntGrpId": 55,"coaGrpNm": "Input Gst","coaGrpCode": "CLDTX0503","coaLedger": "Input Gst","prntSeq": 3,"pL_Item": 0,"opening": 9994907.07,"prdDr": 0,"prdCr": 0,"closing": 9994907.07},{"siteCode": "ANKL","ldgrGrp": "L","coaGrpId": 66,"coaPrntGrpId": 66,"coaGrpNm": "Input Gst","coaGrpCode": "CLDTX050300004","coaLedger": "Input Igst","prntSeq": 3,"pL_Item": 0,"opening": 2116005.13,"prdDr": 0,"prdCr": 0,"closing": 2116005.13},{"siteCode": "ANKL","ldgrGrp": "L","coaGrpId": 66,"coaPrntGrpId": 66,"coaGrpNm": "Input Gst","coaGrpCode": "CLDTX050300006","coaLedger": "Input Cgst","prntSeq": 3,"pL_Item": 0,"opening": 3939450.61,"prdDr": 0,"prdCr": 0,"closing": 3939450.61},{"siteCode": "ANKL","ldgrGrp": "L","coaGrpId": 66,"coaPrntGrpId": 66,"coaGrpNm": "Input Gst","coaGrpCode": "CLDTX050300008","coaLedger": "Input Sgst","prntSeq": 3,"pL_Item": 0,"opening": 3939451.33,"prdDr": 0,"prdCr": 0,"closing": 3939451.33}];

const result = Object.values(
    Tridata.reduce(function (acc, { ldgrGrp, coaGrpCode, coaLedger, ...nums}) {
        const key = JSON.stringify([ldgrGrp, coaGrpCode, coaLedger]);
        const group = (acc[key] ??= {
            ldgrGrp, coaGrpCode, coaLedger,
            opening: 0,
            prdDr: 0,
            prdCr: 0,
            closing: 0
        });
        group.opening += nums.opening;
        group.prdDr += nums.prdDr;
        group.prdCr += nums.prdCr;
        group.closing += nums.closing;
        return acc;
    }, {})
);

console.log(result);

trincot0
Related