ORIGINAL POST:
I've created a fiddle to demonstrate how my data comes from JSON and renders in a ColumnChart.
https://jsfiddle.net/w4dokdt9/3/
My JSON comes over like this:
[{"lPlusScoreID":1,"jan":60.03,"feb":43.57,"mar":48.55},
{"lPlusScoreID":2, "jan":89.42,"feb":85.71,"mar":90.46},
{"lPlusScoreID":3,"jan":86.22,"feb":90.61,"mar":89.53}]
dataArray_input
dataArray_input
transposeArray()
dataArray_trans
function transposeArray(a) {
return Object.keys(a[0]).map(function (c) {
return a.map(function (r) {
return r[c];
});
}); }
dataArray_trans
appendArray()
dataArray_output
function appendArray(a, b) {
for (var i = 0; i < a.length; i++) {
b.push(a[i]);
} }
// Load the Visualization API and the corechart package.
google.charts.load('current', {
'packages': ['corechart', 'table', 'gauge', 'controls']
});
// Set a callback to run when the Google Visualization API is loaded.
google.charts.setOnLoadCallback(gChart0);
function gChart0() {
drawChartXYZ();
};
/*-----------------------------------------------------------------------------*/
// Remove Duplicates in singleDimensionalArray
//-----------------------------------------------------------------------------*/
function onlyUnique(value, index, self) {
return self.indexOf(value) === index;
}
/*-----------------------------------------------------------------------------*/
// Transpose Array
/* Source1: https://jsfiddle.net/w4dokdt9/6/
Source2: http://geniuscarrier.com/transpose-in-javascript/
Call: a = array to transpose
-------------------------------------------------------------------------------*/
function transposeArray(a) {
return Object.keys(a[0]).map(function (c) {
return a.map(function (r) {
return r[c];
});
});
}
/*-----------------------------------------------------------------------------*/
// Append Array
/* Source1: https://davidwalsh.name/combining-js-arrays
Call: a = source, b = destination "a onto b"
-------------------------------------------------------------------------------*/
function appendArray(a, b) {
for (var i = 0; i < a.length; i++) {
b.push(a[i]);
}
}
/*-----------------------------------------------------------------------------*/
// Google Function
//-----------------------------------------------------------------------------*/
function drawChartXYZ() {
//var urlString = '../mps/forecastVols' + urlParameter1 + urlParameter1_Value;
//var urlString_temp = 'https://jsonplaceholder.typicode.com/users';
var urlString_temp = 'https://httpbin.org/get'; //source: https://resttesttest.com/
$.ajax({
type: 'GET',
dataType: 'json',
contentType: "application/json",
//url: urlString,
url: urlString_temp,
success: function (result) {
//Manually loaded "result" with JSON that normally comes from "urlString".
result = [{"name":"Sensor","techName":"GS_SI","p1":305901.00,"p2":343653.00,"p3":414684.00,"p4":324323.00,"p5":366401.00,"p6":369596.00,"p7":273567.00,"p8":407767.00,"p9":396620.00,"p10":434000.00,"p11":392000.00,"p12":336000.00,"p13":420000.00,"p14":378000.00,"p15":434000.00,"p16":420000.00,"p17":420000.00,"p18":434000.00,"p19":336000.00,"p20":434000.00,"p21":406000.00,"p22":0.00,"p23":0.00,"p24":0.00,"p25":358884.00,"p26":335730.00,"p27":358884.00,"p28":347307.00,"p29":358884.00,"p30":347307.00,"p31":361409.00,"p32":361409.00,"p33":349751.00,"p34":361409.00,"p35":349751.00,"p36":361409.00,"p37":0.00,"p38":0.00,"p39":0.00,"p40":0.00,"p41":0.00,"p42":0.00,"p43":0.00,"p44":0.00,"p45":0.00,"p46":0.00,"p47":0.00,"p48":0.00,"id":2,"b1":null,"b2":null}];
//Declare Array Variables
var dataArray_ID = []; //ID List
var dataArray_input = []; //Input
var dataArary_trans = []; //Transpose
var dataArray_output = []; //Output
//Unique product ID's from JSON result
$.each(result, function (j, obj) {
dataArray_ID.push([
obj.id
]);
});
dataArray_ID.filter(onlyUnique);
//Loop for each ID: Input, Transpose, Output
for (var j = 0; j < dataArray_ID.length; ++j) {
dataArray_input = [];
dataArray_input.push([
'Jan', 'Feb', 'Mar', 'Apr', 'May', 'Jun', 'Jul', 'Aug', 'Sep', 'Oct', 'Nov', 'Dec'
]);
//Input Array
$.each(result, function (i, obj) {
if (obj.id == dataArray_ID[j]) {
dataArray_input.push([
//"Line ID",
obj.id,
obj.id, obj.id, obj.id, obj.id, obj.id, obj.id,
obj.id, obj.id, obj.id, obj.id, obj.id
]);
dataArray_input.push([
//"Prod CY",
obj.p1, //JAN_CY
obj.p2, obj.p3, obj.p4, obj.p5, obj.p6, obj.p7,
obj.p8, obj.p9, obj.p10, obj.p11, obj.p12
]);
dataArray_input.push([
//"BP CY",
obj.p25, //JAN_CY_BP
obj.p26, obj.p27, obj.p28, obj.p29, obj.p30, obj.p31,
obj.p32, obj.p33, obj.p34, obj.p35, obj.p36
]);
dataArray_input.push([
//"Prod CY+1",
obj.p13, //JAN_CYPLUS1
obj.p14, obj.p15, obj.p16, obj.p17, obj.p18, obj.p19,
obj.p20, obj.p21, obj.p22, obj.p23, obj.p24
]);
dataArray_input.push([
//"BP CY+1",
obj.p37, //JAN_CYPLUS1_BP
obj.p38, obj.p39, obj.p40, obj.p41, obj.p42, obj.p43,
obj.p44, obj.p45, obj.p46, obj.p47, obj.p48
]);
} //END if
}); //END $.each(result, function (i, obj) {
}; //END for (var j = 0; j < dataArray_ID.length; ++j) {
//Transpose Array
dataArray_trans = transposeArray(dataArray_input);
//Append Array to Output
appendArray(dataArray_trans, dataArray_output);
//Create DataTable
var data = new google.visualization.DataTable();
//Add Columns
data.addColumn('string', 'Month');//0
data.addColumn('number', 'Line ID');//1
data.addColumn('number', 'Prod CY');//2
data.addColumn('number', 'BP CY');//3
data.addColumn('number', 'Prod CY+1');//4
data.addColumn('number', 'BP CY+1');//5
data.addRows(dataArray_output);
//Create Data View
var viewFilter = new google.visualization.DataView(data);
viewFilter.setColumns([0, 1, 2, 3, 4, 5]);
viewFilter.setRows(viewFilter.getFilteredRows([{ column: 1, value: 2 }]));
var viewTable = new google.visualization.DataView(viewFilter);
viewTable.setColumns([0, 2, 3, 4, 5]);
var viewChart1 = new google.visualization.DataView(viewFilter);
viewChart1.setColumns([0, 2, 3]);
//Options
var optionsTable = {
};
var optionsChart1 = {
title: 'Volume Development - CY',
vAxis: {
title: 'Volume',
minValue: '0'
},
series: {
0: { type: 'bars' }, //Prod CY
1: { type: 'line' }, //BP CY
}
};
//Instantiate and draw chart, passing in options.
var table = new google.visualization.Table(document.getElementById('table_div'));
table.draw(viewTable, optionsTable);
var chart1 = new google.visualization.ComboChart(document.getElementById('div_chart1'));
chart1.draw(viewChart1, optionsChart1);
} //END success: function (result) {
}); //END $.ajax({
} //END function drawChart()
<script src="https://ajax.googleapis.com/ajax/libs/jquery/2.1.1/jquery.min.js"></script>
<script src="https://www.gstatic.com/charts/loader.js"></script>
<div id="table_div"></div>
<hr />
<div id="div_chart1"></div>
with google charts, it's all in how the data is fed to the chart
there are data manipulation methods for group()
and join()
but not any to pivot from columns to rows, etc...
for the requested chart, the data will need to be structured as follows...
['month', 'Product1', 'Product2']
['jan', 60.03, 89.42]
['feb', 43.57, 85.71]
see following working snippet...
the data table is created with a single column for month
a new column is added for each "lPlusScoreID"
data table method getFilteredRows
is used to see if the month has already been added as row
if so, the row is updated
otherwise, a new row is added
google.charts.load('current', {
packages: ['corechart']
}).then(function () {
var jsonData = [
{"lPlusScoreID":1, "jan":60.03, "feb":43.57, "mar":48.55},
{"lPlusScoreID":2, "jan":89.42, "feb":85.71, "mar":90.46},
{"lPlusScoreID":3, "jan":86.22, "feb":90.61, "mar":89.53}
];
var data = new google.visualization.DataTable();
data.addColumn('string', 'Month');
// build data
jsonData.forEach(function (row) {
var colIndex;
var rowIndex;
// process each key in the row object
Object.keys(row).forEach(function (key) {
// add column for ID
if (key === 'lPlusScoreID') {
colIndex = data.addColumn('number', 'Product' + row.lPlusScoreID.toString());
return;
}
// find / add row for month
rowIndex = getRowIndex(key);
if (rowIndex === null) {
rowIndex = data.addRow();
data.setValue(rowIndex, 0, key);
}
data.setValue(rowIndex, colIndex, row[key]);
});
});
function getRowIndex(rowMonth) {
var rowIndex = data.getFilteredRows([{
column: 0,
value: rowMonth
}]);
if (rowIndex.length > 0) {
return rowIndex[0];
}
return null;
}
var chart = new google.visualization.ColumnChart(document.getElementById('chart_div'));
chart.draw(data);
});
<script src="https://www.gstatic.com/charts/loader.js"></script>
<div id="chart_div"></div>