Re: Display data as column chart

[asgallant]

I'm not sure I understood this quite right, but lets see if this works for you.  It sounds like you need to group datatable2 on EmpNo and CostID to get the sum of BudgetNo, then join the grouped data with datatable1.  The join would have EmpNo, BudgetNo, and CostID from datatable1 and BudgetNo from the grouped data.  You can then create a DataView based on the joined data which has a column calculated as the difference between the two BudgetNo columns.

On Sunday, November 25, 2012 1:06:02 PM UTC-5, sats wrote:

 

Hello All,

I am new to google visualization. I want to display data from two lists combinely as output to Column Chart.

 I have two lists master and parent list and based on the data from that,  I want to create a column chart. I created a javascript file added google and required reference.

I created two datatables

datatble1 (master data unique rows ) and datatble2 (child rows data multiple entries) as

var dataJobNumber = new google.visualization.DataTable();  
  dataJobNumber.addColumn('number', 'Emp No');
  dataJobNumber.addColumn('number', 'Budget Amount');
  dataJobNumber.addColumn('number', 'CostID');  

 

The data I am getting in format as for both datatables

row : 12345,111111,4444444.11111 and similarly data in other rows

 

In datatable 2 there are multiple entries for empno and costID (e.g: 12345 as empno and 4444444.11111).

I want to have a datatble that will be created based on the datable1 and datatble2 say as dattable3 which will actually generate columnchart.

datatble2 e.g:

EmpNo BudgetNo CostID

12345,111111,4444444.11111

12345,111111,4444444.11111  //// and other rows

In this case (I tried group function) I want output as

12345,222222,4444444.11111 (since all multiple entries based on empno and costId should get added for budget amount value).

Then this Budget amount for example 222222 for empid 1234 and costid 444444.11111 I want to substract from master table datatble and get that value output in datatble3

 

final datatble should be (emp no , budget amount (master table budget emount - sum (all budget amount for empid and costid), costid

 

The data in example is dummy, but it will be in that very same format like (11111,111111.2222224,13232)

 

Please help me on this

thanks in advance 

 

[sats]

Thanks for the reply. I think that was rightly understood by you. If please you can explain how can group by the columns then taking the difference return the result, that will be helpful.
thanks

[asgallant]

Here's some code to get you started:

/* group the data in datatable2
 * columns:
 *     0 is EmpNo
 *     1 is Budget Amount
 *     2 is CostID
 * grouping by 0 and 2 to get the sum of 1
 */
var groupedData = google.visualization.data.group(datatable2, [0, 2], [{
    column: 1,
    type: 'number',
    label: datatable2.getColumnLabel(1),
    aggregation: google.visualization.data.sum
}]);

// join datatable1 and groupedData together
// note that column order is different in groupedData than is was in datatable2 (EmpNo, CostID, Budget Amount)
// I used a "inner" join here, which only includes rows where the keys are found in both datatable1 and 
// groupedData, but you might want to use another join type.  See the list of types here: 
// https://developers.google.com/chart/interactive/docs/reference#google_visualization_data_join
var joinedData = google.visualization.data.join(datatable1, groupedData, 'inner', [
    [0, 0], // match column zero in both tables
    [2, 1]  // match column 2 in datatable1 with column 1 in groupedData
], [1], [2]); // include column 1 from datatable1 and column 2 from groupedData

// create a DataView which subtracts the grouped budget amount from the datatable1 budget amount
var view = new google.visualization.DataView(joinedData);
view.setColumns([0, {
    type: 'number',
    label: joinedData.getColumnLabel(2),
    calc: function (dt, row) {
        var master = dt.getValue(row, 2);
        var grouped = dt.getValue(row, 3);
        return master - grouped;
    }
}, 1]);

Use the view to draw your chart instead of the DataTables.

I haven't tested this code, so it's probably not bug/error free, but it should be good enough to give you a start.