Reputation: 4126
I've got this array of objects
const raw = [
{
wsDescPL: 'DB276',
glFull: 'Refunds',
amount: 469.34,
adjAmount: 469.34
},
{
wsDescPL: 'DB276',
glFull: 'Rent Income',
amount: -2405.02,
adjAmount: -2405.02
},
{
wsDescPL: 'ADMIN',
glFull: 'Sales',
amount: 0,
adjAmount: 0
},
{
wsDescPL: 'DB276',
glFull: 'Sales',
amount: 2146187.47,
adjAmount: 2146187.47
},
{
wsDescPL: 'WAREHOUSE',
glFull: 'Sales',
amount: 0,
adjAmount: 0
},
{
wsDescPL: 'ADMIN',
glFull: 'Sales Variance',
amount: 0,
adjAmount: 0
},
{
wsDescPL: 'DB276',
glFull: 'Sales Variance',
amount: -5369,
adjAmount: -5369
},
{
wsDescPL: 'WAREHOUSE',
glFull: 'Sales Variance',
amount: 0,
adjAmount: 0
}
]
I'm trying to get it into the format of:
const desired = [
['', 'ADMIN', 'DB276', 'WAREHOUSE', 'TOTAL']
['Refunds', 0, 469.34, 0, 469.34]
['Rent Income', 0, -2405.02, 0, -2405.02]
['Sales', 0, 2146187.47, 0, 2146187]
['Sales Variance', 0, -5369, 0, -5369]
]
My issue is that admin and warehouse may or may not have a "refunds" (or other gl code) entry.
Another layer is that the glFull
is not universal. Other locations may have different gl's.
I've been going crazy trying to figure out how to get it in the desired layout.
It needs to be in a multidimensional array because of the way the UI is displaying the table.
If I change it, I'd have to change a whole hell of a lot.
Upvotes: 0
Views: 79
Reputation: 128
This code works, but probably not the most optimized way to do this.
const raw = [{
wsDescPL: 'DB276',
glFull: 'Refunds',
amount: 469.34,
adjAmount: 469.34
},
{
wsDescPL: 'DB276',
glFull: 'Rent Income',
amount: -2405.02,
adjAmount: -2405.02
},
{
wsDescPL: 'ADMIN',
glFull: 'Sales',
amount: 0,
adjAmount: 0
},
{
wsDescPL: 'DB276',
glFull: 'Sales',
amount: 2146187.47,
adjAmount: 2146187.47
},
{
wsDescPL: 'WAREHOUSE',
glFull: 'Sales',
amount: 0,
adjAmount: 0
},
{
wsDescPL: 'ADMIN',
glFull: 'Sales Variance',
amount: 0,
adjAmount: 0
},
{
wsDescPL: 'DB276',
glFull: 'Sales Variance',
amount: -5369,
adjAmount: -5369
},
{
wsDescPL: 'WAREHOUSE',
glFull: 'Sales Variance',
amount: 0,
adjAmount: 0
}
]
const generatePivot = () => {
let markers = ['ADMIN', 'DB276', 'WAREHOUSE'];
let header = ['', 'ADMIN', 'DB276', 'WAREHOUSE', 'TOTAL'];
let glFullListAll = [];
let dataTab = {};
//Process data into a JS Object
raw.forEach((item, i) => {
if (!dataTab[item.wsDescPL])
dataTab[item.wsDescPL] = {}
if (!dataTab[item.glFull]) {
dataTab[item.wsDescPL][item.glFull] = {
amount: 0,
adjAmount: 0
};
glFullListAll.push(item.glFull);
}
dataTab[item.wsDescPL][item.glFull]["amount"] += item.amount;
dataTab[item.wsDescPL][item.glFull]["adjAmount"] += item.adjAmount;
})
//console.log(dataTab);
//Convert JS Object to Array of Arrays
let glFullList = new Set(glFullListAll);
let desired = []
desired.push(header);
glFullList.forEach((g, i) => {
let temp = [];
let total = 0;
temp.push(g);
markers.forEach((key, i) => {
if (dataTab[key][g]) {
temp.push(dataTab[key][g]["adjAmount"]);
total += parseFloat(dataTab[key][g]["adjAmount"]);
} else
temp.push(0);
})
temp.push(total);
desired.push(temp);
})
console.log(desired);
return desired;
}
generatePivot();
Upvotes: 1
Reputation: 21658
const raw = [
{
wsDescPL: 'DB276',
glFull: 'Refunds',
amount: 469.34,
adjAmount: 469.34
},
{
wsDescPL: 'DB276',
glFull: 'Rent Income',
amount: -2405.02,
adjAmount: -2405.02
},
{
wsDescPL: 'ADMIN',
glFull: 'Sales',
amount: 0,
adjAmount: 0
},
{
wsDescPL: 'DB276',
glFull: 'Sales',
amount: 2146187.47,
adjAmount: 2146187.47
},
{
wsDescPL: 'WAREHOUSE',
glFull: 'Sales',
amount: 0,
adjAmount: 0
},
{
wsDescPL: 'ADMIN',
glFull: 'Sales Variance',
amount: 0,
adjAmount: 0
},
{
wsDescPL: 'DB276',
glFull: 'Sales Variance',
amount: -5369,
adjAmount: -5369
},
{
wsDescPL: 'WAREHOUSE',
glFull: 'Sales Variance',
amount: 0,
adjAmount: 0
}
];
const transform = raw => raw.reduce((results, item) => {
results.push([ item.glFull, item.amount - item.adjAmount, item.amount, item.adjAmount ]);
return results;
}, [['', 'ADMIN', 'DB276', 'WAREHOUSE', 'TOTAL']]);
console.log(transform(raw));
Upvotes: 1
Reputation: 16968
You want to use a combination of Array.prototype.forEach
and Object.values
like so:
const arr = [];
raw.forEach((item) => {
arr.push(Object.values(item));
});
console.log(arr);
If there are optional fields in the response then I would advise returning null values from the API instead of padding the array using JS.
However, if you have no choice, then the only way I can see doing it is like so (this is pretty dirty):
const arr = [];
raw.forEach((item, index) => {
arr[index] = [
item.hasOwnProperty('wsDescPL') ? item.wsDescPL : '',
item.hasOwnProperty('glFull') ? item.glFull : '',
item.hasOwnProperty('amount') ? item.amount : '',
item.hasOwnProperty('adjAmount') ? item.adjAmount : ''
];
});
console.log(arr);
Upvotes: 1