Milano Slesarik Milano Slesarik - 18 days ago 9
Javascript Question

Google Visualisation - how to join multiple DataTables?

I'm trying to create a chart with multiple lines with Google Visualisation Api. I use JSON to add data into the Chart.

There are multiple DataTables created at the beginning - the number of tables is equal to number of JSON keys.

EDIT: I've found the answer for the original problem - without loop. SO Answer - the answer is about drawing two lines in one chart. In the comment, there is an answer how to do this with multiple lines - this JSFiddle: JSFiddle - Multiple lines But I can't still figure out how to use this answer in my case.

The problem is that it draws only two lines (first and last)? I suppose that it's caused in this line:

joinedData = google.visualization.data.join(joinedData, datatable, 'full', [[0, 0]], [1], [1]);


Which is a try to join all DataTables, so all lines should be visible on a chart. But in fact, I don't have a clue why it doesn't work correctly.

JOIN Doc: Join

This is the whole joining loop:

var joinedData = datas[0];
$.each(datas, function (index, data1) {
if (index != 0) {
joinedData = google.visualization.data.join(joinedData, data1, 'full', [[0, 0]], [1], [1]);
}
});


And this is the whole script - or better JSFIDDLE

function drawChart() {
var google_chart_json = {"Whirpool.cz": [[[2016, 11, 2], 156.0], [[2016, 11, 3], 69.0], [[2016, 11, 4], 126.0], [[2016, 11, 5], 67.0], [[2016, 11, 6], 97.0], [[2016, 11, 7], 193.0], [[2016, 11, 8], 96.0], [[2016, 11, 9], 64.0], [[2016, 11, 10], 117.0], [[2016, 11, 11], 190.0]], "Mall.cz": [[[2016, 11, 2], 95.0], [[2016, 11, 3], 98.0], [[2016, 11, 4], 158.0], [[2016, 11, 5], 160.0], [[2016, 11, 6], 191.0], [[2016, 11, 7], 106.0], [[2016, 11, 8], 51.0], [[2016, 11, 9], 158.0], [[2016, 11, 10], 89.0], [[2016, 11, 11], 154.0]], "Mall.sk": [[[2016, 11, 2], 72.0], [[2016, 11, 3], 108.0], [[2016, 11, 4], 175.0], [[2016, 11, 5], 81.0], [[2016, 11, 6], 123.0], [[2016, 11, 7], 188.0], [[2016, 11, 8], 52.0], [[2016, 11, 9], 196.0], [[2016, 11, 10], 199.0], [[2016, 11, 11], 117.0]], "Teshop.cz": [[[2016, 11, 2], 93.0], [[2016, 11, 3], 71.0], [[2016, 11, 4], 59.0], [[2016, 11, 5], 101.0], [[2016, 11, 6], 86.0], [[2016, 11, 7], 139.0], [[2016, 11, 8], 110.0], [[2016, 11, 9], 75.0], [[2016, 11, 10], 183.0], [[2016, 11, 11], 156.0]], "Gamepro.cz": [[[2016, 11, 2], 89.0], [[2016, 11, 3], 104.0], [[2016, 11, 4], 62.0], [[2016, 11, 5], 190.0], [[2016, 11, 6], 172.0], [[2016, 11, 7], 109.0], [[2016, 11, 8], 183.0], [[2016, 11, 9], 88.0], [[2016, 11, 10], 89.0], [[2016, 11, 11], 106.0]], "Gamepro.sk": [[[2016, 11, 2], 133.0], [[2016, 11, 3], 173.0], [[2016, 11, 4], 102.0], [[2016, 11, 5], 151.0], [[2016, 11, 6], 119.0], [[2016, 11, 7], 172.0], [[2016, 11, 8], 139.0], [[2016, 11, 9], 177.0], [[2016, 11, 10], 174.0], [[2016, 11, 11], 141.0]], "Datart.cz": [[[2016, 11, 2], 77.0], [[2016, 11, 3], 120.0], [[2016, 11, 4], 116.0], [[2016, 11, 5], 60.0], [[2016, 11, 6], 179.0], [[2016, 11, 7], 162.0], [[2016, 11, 8], 151.0], [[2016, 11, 9], 123.0], [[2016, 11, 10], 138.0], [[2016, 11, 11], 109.0]], "Ockosice.sk": [[[2016, 11, 2], 83.0], [[2016, 11, 3], 111.0], [[2016, 11, 4], 185.0], [[2016, 11, 5], 199.0], [[2016, 11, 6], 133.0], [[2016, 11, 7], 142.0], [[2016, 11, 8], 61.0], [[2016, 11, 9], 74.0], [[2016, 11, 10], 168.0], [[2016, 11, 11], 128.0]], "Bestmarket.sk": [[[2016, 11, 2], 174.0], [[2016, 11, 3], 154.0], [[2016, 11, 4], 180.0], [[2016, 11, 5], 87.0], [[2016, 11, 6], 193.0], [[2016, 11, 7], 105.0], [[2016, 11, 8], 106.0], [[2016, 11, 9], 115.0], [[2016, 11, 10], 185.0], [[2016, 11, 11], 159.0]]};
var datas = [];
$.each(google_chart_json, function (key, val) {
var domain = key;
var data = new google.visualization.DataTable();
data.addColumn('date', domain + 'Date');
data.addColumn('number', domain);


$.each(val, function (_, scan) {
var year = scan[0][0];
var month = scan[0][1];
var day = scan[0][2];
var price = scan[1];

data.addRows([
[new Date(year, month, day), price]
]);
});
datas.push(data);
});
var joinedData = datas[0];
$.each(datas, function (index, datatable) {
console.log('joining index '+index)
if (index != 0) {
joinedData = google.visualization.data.join(joinedData, datatable, 'full', [[0, 0]], [1], [1]);
}
});


var chart = new google.visualization.LineChart(document.querySelector('#chart_div'));
chart.draw(joinedData, {
height: 300,
width: 600,
interpolateNulls: true,
explorer: {
maxZoomOut: 2,
keepInBounds: true
},
});
}
google.load('visualization', '1', {packages: ['corechart'], callback: drawChart});

Answer

first, need to use the latest library (loader.js)
according to the release notes...

The version of Google Charts that remains available via the jsapi loader is no longer being updated consistently. Please use the new gstatic loader from now on.

include this...
<script src="https://www.gstatic.com/charts/loader.js"></script>

not this...
<script src="http://www.google.com/jsapi"></script>

this will change the load statement from...
google.load('visualization', '1', {packages: ['corechart'], callback: drawChart});

to...
google.charts.load('current', {packages: ['corechart'], callback: drawChart});

next, the reason only two lines (columns) make it to the final data table,
has to do with the 5th argument (dt1Columns)
with each join, only the first data column is included [1] from the first table

instead, the argument should grow with each join
[1], [1,2], [1,2,3]

see following working snippet...

function drawChart() {
    var google_chart_json = {"Whirpool.cz": [[[2016, 11, 2], 156.0], [[2016, 11, 3], 69.0], [[2016, 11, 4], 126.0], [[2016, 11, 5], 67.0], [[2016, 11, 6], 97.0], [[2016, 11, 7], 193.0], [[2016, 11, 8], 96.0], [[2016, 11, 9], 64.0], [[2016, 11, 10], 117.0], [[2016, 11, 11], 190.0]], "Mall.cz": [[[2016, 11, 2], 95.0], [[2016, 11, 3], 98.0], [[2016, 11, 4], 158.0], [[2016, 11, 5], 160.0], [[2016, 11, 6], 191.0], [[2016, 11, 7], 106.0], [[2016, 11, 8], 51.0], [[2016, 11, 9], 158.0], [[2016, 11, 10], 89.0], [[2016, 11, 11], 154.0]], "Mall.sk": [[[2016, 11, 2], 72.0], [[2016, 11, 3], 108.0], [[2016, 11, 4], 175.0], [[2016, 11, 5], 81.0], [[2016, 11, 6], 123.0], [[2016, 11, 7], 188.0], [[2016, 11, 8], 52.0], [[2016, 11, 9], 196.0], [[2016, 11, 10], 199.0], [[2016, 11, 11], 117.0]], "Teshop.cz": [[[2016, 11, 2], 93.0], [[2016, 11, 3], 71.0], [[2016, 11, 4], 59.0], [[2016, 11, 5], 101.0], [[2016, 11, 6], 86.0], [[2016, 11, 7], 139.0], [[2016, 11, 8], 110.0], [[2016, 11, 9], 75.0], [[2016, 11, 10], 183.0], [[2016, 11, 11], 156.0]], "Gamepro.cz": [[[2016, 11, 2], 89.0], [[2016, 11, 3], 104.0], [[2016, 11, 4], 62.0], [[2016, 11, 5], 190.0], [[2016, 11, 6], 172.0], [[2016, 11, 7], 109.0], [[2016, 11, 8], 183.0], [[2016, 11, 9], 88.0], [[2016, 11, 10], 89.0], [[2016, 11, 11], 106.0]], "Gamepro.sk": [[[2016, 11, 2], 133.0], [[2016, 11, 3], 173.0], [[2016, 11, 4], 102.0], [[2016, 11, 5], 151.0], [[2016, 11, 6], 119.0], [[2016, 11, 7], 172.0], [[2016, 11, 8], 139.0], [[2016, 11, 9], 177.0], [[2016, 11, 10], 174.0], [[2016, 11, 11], 141.0]], "Datart.cz": [[[2016, 11, 2], 77.0], [[2016, 11, 3], 120.0], [[2016, 11, 4], 116.0], [[2016, 11, 5], 60.0], [[2016, 11, 6], 179.0], [[2016, 11, 7], 162.0], [[2016, 11, 8], 151.0], [[2016, 11, 9], 123.0], [[2016, 11, 10], 138.0], [[2016, 11, 11], 109.0]], "Ockosice.sk": [[[2016, 11, 2], 83.0], [[2016, 11, 3], 111.0], [[2016, 11, 4], 185.0], [[2016, 11, 5], 199.0], [[2016, 11, 6], 133.0], [[2016, 11, 7], 142.0], [[2016, 11, 8], 61.0], [[2016, 11, 9], 74.0], [[2016, 11, 10], 168.0], [[2016, 11, 11], 128.0]], "Bestmarket.sk": [[[2016, 11, 2], 174.0], [[2016, 11, 3], 154.0], [[2016, 11, 4], 180.0], [[2016, 11, 5], 87.0], [[2016, 11, 6], 193.0], [[2016, 11, 7], 105.0], [[2016, 11, 8], 106.0], [[2016, 11, 9], 115.0], [[2016, 11, 10], 185.0], [[2016, 11, 11], 159.0]]};
    var datas = [];
    $.each(google_chart_json, function (key, val) {
        var domain = key;
        var data = new google.visualization.DataTable();
        data.addColumn('date', domain + 'Date');
        data.addColumn('number', domain);

        $.each(val, function (_, scan) {
            var year = scan[0][0];
            var month = scan[0][1];
            var day = scan[0][2];
            var price = scan[1];

            data.addRows([
                [new Date(year, month, day), price]
            ]);
        });
        datas.push(data);
    });
    var joinedData = datas[0];
    var columns = [];
    $.each(datas, function (index, datatable) {
        if (index != 0) {
            columns.push(index);
            joinedData = google.visualization.data.join(joinedData, datatable, 'full', [[0, 0]], columns, [1]);
        }
    });

    var chart = new google.visualization.LineChart(document.querySelector('#chart_div'));
    chart.draw(joinedData, {
        height: 300,
        width: 600,
        interpolateNulls: true,
        explorer: {
            maxZoomOut: 2,
            keepInBounds: true
        },
    });
}
google.charts.load('current', {packages: ['corechart'], callback: drawChart});
<script src="https://code.jquery.com/jquery-1.12.4.js"></script>
<script src="https://www.gstatic.com/charts/loader.js"></script>
<div id="chart_div"></div>