Dynamic Kendo grid columns from response data

Question

I have been trying to create a Kendo grid with dynamic column values based on a date item as part of the response data.

The data I have looks like this:

[
    { Date: '01-01-2018', Name: 'Foo', Value: 1000},
    { Date: '02-01-2018', Name: 'Foo', Value: 2000},
    { Date: '03-01-2018', Name: 'Foo', Value: 3000},
    { Date: '01-01-2018', Name: 'Bar', Value: 1400},
    { Date: '02-01-2018', Name: 'Bar', Value: 2000},
    { Date: '03-01-2018', Name: 'Bar', Value: 5000}
]

My intended structure for the grid is the following:

| Name |  Jan | Feb  | Mar  |
|------|------|------|------|
| Foo  | 1000 | 2000 | 3000 |
| Bar  | 1400 | 2000 | 5000 |

I had a look at https://docs.telerik.com/kendo-ui/controls/data-management/grid/how-to/various/create-with-dynamic-columns-and-data-types but it was not quite what I was trying to do and it required that I have the columns sent as part of the response.

I am working with a wrapper for GridOptions that populates the columns through a staticly defined json. Since my columns are dynamic I am having an issue with defining them there.

On top of that, I am unable to pick out the values for the date besides brute forcing through the values and storing all the unique date entries as columns. And if I have them, then how do I match them up with the correct data entry to display the correct value in the grid?

Richard Richard · Accepted Answer · 2018-03-08T06:00:22

You could use the kendo ui PivotGrid component. It is built for dealing with categorical data. However, you probably would find that takes up to much real estate.

That leaves the task of manually pivoting the data yourself. A task that is relatively simple if you make the assumptions that the Date values over all the data never have a month from two different years (If there was a 01-01-2018 and 01-01-2017 they are both Jan) and the is only one row for each date/name combo. (If there were two values for a date/name you would have to decide what is done with the value? min, max, first, last, mean ?)

data = 
[
    { Date: '01-01-2018', Name: 'Foo', Value: 1000},
    { Date: '02-01-2018', Name: 'Foo', Value: 2000},
    { Date: '03-01-2018', Name: 'Foo', Value: 3000},
    { Date: '01-01-2018', Name: 'Bar', Value: 1400},
    { Date: '02-01-2018', Name: 'Bar', Value: 2000},
    { Date: '03-01-2018', Name: 'Bar', Value: 5000}
];

// distinct month nums over all data
months = [];
data.forEach(function(item) {
  var parts = item.Date.split('-');
  var month = parts[0] - 1;
  if (months.indexOf(month) == -1) months.push(month);
});

// sort and convert month num to month name (for columns)
months.sort();
months.forEach(function(monthNum,index,arr) {
  arr[index] = new Date(2018,monthNum,1).toLocaleString("en-US", { month: "short" });
});

function mmddyyyyToMon(mmddyyyy) {
  var parts = mmddyyyy.split("-");
  var jsMonth = parts[0] - 1;
  var jsDay = parts[1];
  var jsYear = parts[2];
  return new Date(jsYear,jsMonth,jsDay).toLocaleString("en-US", { month: "short" });
}

// helper to make sure pivot item has one field for every month observed over all data
function newPivotItem () {
  var result = { Name: '' };
  months.forEach(function(month) {
    result[month] = undefined;
  })
  return result;
}

// presume data grouped by Name and ordered by month within
var pivotData = [];
var pivotItem = {};
data.forEach (function (item) {
  var parts = item.Date.split("-");
  var jsMonth = parts[0] - 1;
  var jsDay = parts[1];
  var jsYear = parts[2];
  var month = new Date(jsYear,jsMonth,jsDay).toLocaleString("en-US", { month: "short" });

  if (pivotItem.Name != item.Name) {
    // start next group of data for a name
    pivotItem = newPivotItem();
    pivotData.push(pivotItem);
    pivotItem.Name = item.Name;
  }

  // set value for month for the name
  pivotItem[month] = item.Value;
})

console.log (pivotData);

Dynamic Kendo grid columns from response data

2 Answers