I need to display the data in a table in this way from what I have now.
The data object I'm getting looks like this
I am using mui x-data grid table to display the table:
<DataGrid
rows={recon_rows}
columns={recon_columns}
/>
And I'm getting the data displayed in the table in the current state with this code:
var recon_rows = [];
var recon_columns = [];
var setHeaders = new Set();
var setDate = new Set();
for(let i = 0; i < reportData.length; i++) {
setHeaders.add(reportData[i].hdrname);
setDate.add(reportData[i].recondate);
}
recon_columns = [
{ field: 'col1', headerName: 'Date', minWidth: 100 },
{ field: 'col2', headerName: 'Charge', minWidth: 100 },
];
for(var headers of setHeaders) {
recon_columns.push({
field: headers,
headerName: headers,
minWidth: 150
});
for(var dates of setDate) {
var charge = 0;
for(var i = 0; i < reportData.length; i++) {
if(reportData[i].hdrname === headers && reportData[i].recondate === dates) {
charge += reportData[i].entryqty;
recon_rows.push({
id: Math.random(dates),
col1: dates,
col2: charge,
[headers]: charge
});
}
}
}
}
If anyone has any suggestions of how adjust the code to get it display the dates in a date column only once per date and for the rest of the data to follow horizontally I would highly appreciate it. Charge should be the sum of all the other cells in the same row.
Here's the link for the codesandbox
I'm adding the data object from the image above:
[
{
"recondate": "2022-07-19",
"entryclass": 1,
"entryid": 1,
"entryqty": 16,
"entrycharge": 160,
"entryseq": 10001,
"hdrname": "SERV-Major-2S"
},
{
"recondate": "2022-07-21",
"entryclass": 1,
"entryid": 1,
"entryqty": 5,
"entrycharge": 50,
"entryseq": 10001,
"hdrname": "SERV-Major-2S"
},
{
"recondate": "2022-07-20",
"entryclass": 1,
"entryid": 1,
"entryqty": 12,
"entrycharge": 120,
"entryseq": 10001,
"hdrname": "SERV-Major-2S"
},
{
"recondate": "2022-07-19",
"entryclass": 1,
"entryid": 2,
"entryqty": 7,
"entrycharge": 56,
"entryseq": 10002,
"hdrname": "SERV-Minor-2S"
},
{
"recondate": "2022-07-21",
"entryclass": 1,
"entryid": 2,
"entryqty": 2,
"entrycharge": 16,
"entryseq": 10002,
"hdrname": "SERV-Minor-2S"
},
{
"recondate": "2022-07-20",
"entryclass": 1,
"entryid": 2,
"entryqty": 2,
"entrycharge": 16,
"entryseq": 10002,
"hdrname": "SERV-Minor-2S"
},
{
"recondate": "2022-07-20",
"entryclass": 1,
"entryid": 3,
"entryqty": 14,
"entrycharge": 168,
"entryseq": 10003,
"hdrname": "SERV-Major-34S"
},
{
"recondate": "2022-07-19",
"entryclass": 1,
"entryid": 3,
"entryqty": 25,
"entrycharge": 300,
"entryseq": 10003,
"hdrname": "SERV-Major-34S"
},
{
"recondate": "2022-07-21",
"entryclass": 1,
"entryid": 3,
"entryqty": 3,
"entrycharge": 36,
"entryseq": 10003,
"hdrname": "SERV-Major-34S"
},
{
"recondate": "2022-07-20",
"entryclass": 1,
"entryid": 4,
"entryqty": 8,
"entrycharge": 68,
"entryseq": 10004,
"hdrname": "SERV-Minor-34S"
},
{
"recondate": "2022-07-19",
"entryclass": 1,
"entryid": 4,
"entryqty": 12,
"entrycharge": 102,
"entryseq": 10004,
"hdrname": "SERV-Minor-34S"
},
{
"recondate": "2022-07-21",
"entryclass": 1,
"entryid": 4,
"entryqty": 1,
"entrycharge": 8.5,
"entryseq": 10004,
"hdrname": "SERV-Minor-34S"
},
{
"recondate": "2022-07-20",
"entryclass": 2,
"entryid": 2,
"entryqty": 1,
"entrycharge": 3.7,
"entryseq": 20002,
"hdrname": "Wheel-Swl"
},
{
"recondate": "2022-07-19",
"entryclass": 2,
"entryid": 2,
"entryqty": 1,
"entrycharge": 3.7,
"entryseq": 20002,
"hdrname": "Wheel-Swl"
},
{
"recondate": "2022-07-20",
"entryclass": 2,
"entryid": 3,
"entryqty": 1,
"entrycharge": 4.25,
"entryseq": 20003,
"hdrname": "Castor"
},
{
"recondate": "2022-07-19",
"entryclass": 2,
"entryid": 5,
"entryqty": 1,
"entrycharge": 0.6,
"entryseq": 20005,
"hdrname": "Hinge"
},
{
"recondate": "2022-07-20",
"entryclass": 2,
"entryid": 5,
"entryqty": 2,
"entrycharge": 1.2,
"entryseq": 20005,
"hdrname": "Hinge"
},
{
"recondate": "2022-07-21",
"entryclass": 2,
"entryid": 9,
"entryqty": 7,
"entrycharge": 28,
"entryseq": 20009,
"hdrname": "rp-Shelf"
},
{
"recondate": "2022-07-19",
"entryclass": 2,
"entryid": 9,
"entryqty": 40,
"entrycharge": 160,
"entryseq": 20009,
"hdrname": "rp-Shelf"
},
{
"recondate": "2022-07-20",
"entryclass": 2,
"entryid": 9,
"entryqty": 25,
"entrycharge": 100,
"entryseq": 20009,
"hdrname": "rp-Shelf"
},
{
"recondate": "2022-07-19",
"entryclass": 2,
"entryid": 12,
"entryqty": 1,
"entrycharge": 1.3,
"entryseq": 20012,
"hdrname": "Plate"
},
{
"recondate": "2022-07-19",
"entryclass": 2,
"entryid": 13,
"entryqty": 59,
"entrycharge": 0,
"entryseq": 20013,
"hdrname": "rp-Base"
},
{
"recondate": "2022-07-21",
"entryclass": 2,
"entryid": 13,
"entryqty": 11,
"entrycharge": 0,
"entryseq": 20013,
"hdrname": "rp-Base"
},
{
"recondate": "2022-07-20",
"entryclass": 2,
"entryid": 13,
"entryqty": 35,
"entrycharge": 0,
"entryseq": 20013,
"hdrname": "rp-Base"
},
{
"recondate": "2022-07-20",
"entryclass": 2,
"entryid": 14,
"entryqty": 39,
"entrycharge": 0,
"entryseq": 20014,
"hdrname": "rp-Side"
},
{
"recondate": "2022-07-19",
"entryclass": 2,
"entryid": 14,
"entryqty": 90,
"entrycharge": 0,
"entryseq": 20014,
"hdrname": "rp-Side"
},
{
"recondate": "2022-07-21",
"entryclass": 2,
"entryid": 14,
"entryqty": 10,
"entrycharge": 0,
"entryseq": 20014,
"hdrname": "rp-Side"
},
{
"recondate": "2022-07-20",
"entryclass": 2,
"entryid": 15,
"entryqty": 5,
"entrycharge": 0,
"entryseq": 20015,
"hdrname": "rp-FxCast"
},
{
"recondate": "2022-07-19",
"entryclass": 2,
"entryid": 15,
"entryqty": 23,
"entrycharge": 0,
"entryseq": 20015,
"hdrname": "rp-FxCast"
},
{
"recondate": "2022-07-21",
"entryclass": 2,
"entryid": 15,
"entryqty": 3,
"entrycharge": 0,
"entryseq": 20015,
"hdrname": "rp-FxCast"
},
{
"recondate": "2022-07-20",
"entryclass": 2,
"entryid": 16,
"entryqty": 35,
"entrycharge": 0,
"entryseq": 20016,
"hdrname": "rp-WhPlt"
},
{
"recondate": "2022-07-19",
"entryclass": 2,
"entryid": 16,
"entryqty": 44,
"entrycharge": 0,
"entryseq": 20016,
"hdrname": "rp-WhPlt"
},
{
"recondate": "2022-07-21",
"entryclass": 2,
"entryid": 16,
"entryqty": 10,
"entrycharge": 0,
"entryseq": 20016,
"hdrname": "rp-WhPlt"
},
{
"recondate": "2022-07-21",
"entryclass": 2,
"entryid": 17,
"entryqty": 11,
"entrycharge": 0,
"entryseq": 20017,
"hdrname": "rp-AFrm"
},
{
"recondate": "2022-07-20",
"entryclass": 2,
"entryid": 17,
"entryqty": 34,
"entrycharge": 0,
"entryseq": 20017,
"hdrname": "rp-AFrm"
},
{
"recondate": "2022-07-19",
"entryclass": 2,
"entryid": 17,
"entryqty": 39,
"entrycharge": 0,
"entryseq": 20017,
"hdrname": "rp-AFrm"
},
{
"recondate": "2022-07-21",
"entryclass": 2,
"entryid": 18,
"entryqty": 16,
"entrycharge": 0,
"entryseq": 20018,
"hdrname": "Clean-Whl"
},
{
"recondate": "2022-07-20",
"entryclass": 2,
"entryid": 18,
"entryqty": 64,
"entrycharge": 0,
"entryseq": 20018,
"hdrname": "Clean-Whl"
},
{
"recondate": "2022-07-19",
"entryclass": 2,
"entryid": 18,
"entryqty": 143,
"entrycharge": 0,
"entryseq": 20018,
"hdrname": "Clean-Whl"
},
{
"recondate": "2022-07-19",
"entryclass": 2,
"entryid": 19,
"entryqty": 19,
"entrycharge": 0,
"entryseq": 20019,
"hdrname": "rp-Gate"
},
{
"recondate": "2022-07-21",
"entryclass": 2,
"entryid": 19,
"entryqty": 2,
"entrycharge": 0,
"entryseq": 20019,
"hdrname": "rp-Gate"
},
{
"recondate": "2022-07-20",
"entryclass": 2,
"entryid": 19,
"entryqty": 6,
"entrycharge": 0,
"entryseq": 20019,
"hdrname": "rp-Gate"
},
{
"recondate": "2022-07-19",
"entryclass": 2,
"entryid": 31,
"entryqty": 2,
"entrycharge": 1,
"entryseq": 20031,
"hdrname": "sv-Wheel"
},
{
"recondate": "2022-07-20",
"entryclass": 2,
"entryid": 33,
"entryqty": 1,
"entrycharge": 2,
"entryseq": 20033,
"hdrname": "sv-Shelf"
},
{
"recondate": "2022-07-20",
"entryclass": 2,
"entryid": 34,
"entryqty": 1,
"entrycharge": 0.5,
"entryseq": 20034,
"hdrname": "sv-Gate"
}
]
From the structure of the data & how the tables are displayed in your case I believe you need to do this:
var recon_rows = [];
var recon_columns = [];
var setHeaders = new Set();
var setDate = new Set();
for(let i = 0; i < reportData.length; i++) {
setHeaders.add(reportData[i].hdrname);
setDate.add(reportData[i].recondate);
}
recon_columns = [
{ field: 'col1', headerName: 'Date', minWidth: 100 },
{ field: 'col2', headerName: 'Charge', minWidth: 100 },
];
for(var headers of setHeaders) {
recon_columns.push({
field: headers,
headerName: headers,
minWidth: 150
});
for(var dates of setDate) {
var charge = 0;
for(var i = 0; i < reportData.length; i++) {
const date = reportData[i].recondate;
if(reportData[i].hdrname === headers && date === dates) {
charge += reportData[i].entryqty;
// look up for existing row
const existingRow = recon_rows.find((row) => row.col1 === date);
// if we did not find the row
if(!existingRow){
// create it
recon_rows.push({
id: Math.random(dates),
col1: dates,
col2: charge,
[headers]: charge
});
} else {
// if we did find the row, add respective column
existingRow[headers] = charge;
// adjust charges
existingRow.col2 += charge;
}
}
}
}
}
The data output will then look like:
[
{
id: 0.4279088926926955,
col1: '2022-07-19',
col2: 522,
'SERV-Major-2S': 16,
'SERV-Minor-2S': 7,
'SERV-Major-34S': 25,
'SERV-Minor-34S': 12,
'Wheel-Swl': 1,
Hinge: 1,
'rp-Shelf': 40,
Plate: 1,
'rp-Base': 59,
'rp-Side': 90,
'rp-FxCast': 23,
'rp-WhPlt': 44,
'rp-AFrm': 39,
'Clean-Whl': 143,
'rp-Gate': 19,
'sv-Wheel': 2
},
{
id: 0.7232027224695496,
col1: '2022-07-21',
col2: 81,
'SERV-Major-2S': 5,
'SERV-Minor-2S': 2,
'SERV-Major-34S': 3,
'SERV-Minor-34S': 1,
'rp-Shelf': 7,
'rp-Base': 11,
'rp-Side': 10,
'rp-FxCast': 3,
'rp-WhPlt': 10,
'rp-AFrm': 11,
'Clean-Whl': 16,
'rp-Gate': 2
},
...
];
I'm assuming here that in your "desired" screenshot you made a mistake in the "Charge" column, because it seems like it should be sum of all the other columns (except the date one).