Manipulate JSON into specific rows for a table layout

Question

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.

Answer 1

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();

Answer 2

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));

Answer 3

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);