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)
}
CodePudding user response：
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);