Re: Querying the rows from DataTable object

[asgallant]

You are trying to pivot the table, which isn't supported in DataTables.  You can manually pivot the table, though; see example: http://jsfiddle.net/asgallant/HkjDe/ 

On Thursday, July 12, 2012 4:43:50 PM UTC-4, Sudhir Kesharwani wrote:

Visualization Gurus...

I have a data table object in following format.

Equipment	Location▲	Count
e1	c1	6
e2	c1	2
e3	c1	1
e4	c1	3
e1	c2	7
e2	c2	3
e1	c3	8
e2	c3	4

I want to generate a data table from this data onto fixed number of rows and column (missing values should be replaced with 0)

	E1	E2	E3	E4
C1	XX	xx	xx	xx
C2	xx	xx	0	xx
C3	0	xx	xx	xx

Currently I don't see any method of querying details from a DataTable object,  do we have any samples that uses getSelection()

Can I use Query object with DataTable,  all the samples talk to Google Spreadsheet.

google.visualization.Query(dataSourceUrl, opt_options)

Thanks in advance.

[Sudhir Kesharwani]

Thanks Buddy,

It worked like a charm,  thanks a ton. I loved the way you gave me solution. JSFiddle :)

Regards,

Sudhir

[Jasper Leonor]

Is there a way to select the top two columns only? As a bonus, be able to sum the remaining columns into a column called 'Others'

For  example this table:

       E1  E2  E3  E4

C1     9    1   10   2

C2   10    2   15   1

C3     2    4   17   0

The sum of the columns will be 21, 7, 42 and 3 respectively.

The top two columns are E1 and E3, so the resulting table should look like:

       E1  E3  Others

C1     9  10         3

C2   10  15         2

C3     2  17         4

where others is the sum of the remaining columns (e.i: E2 and E4)

[Andrew Gallant]

You can pivot the data as in my eample, and then use a DataView to create your "Others" column:

var othersView = new google.visualization.DataView(pivotedData);

othersView.setColumns([0, 1, 3, {

    type: 'number',

    label: 'Others',

    calc: function (dt, row) {

        return dt.getValue(row, 2) + dt.getValue(row, 4);

    }

}]);

If I understand your intentions correctly, you'll want to dynamically calculate which columns are the top 2.  You'll have to write some logic to determine that, and then you can use something like this:

var top2; // array containing the column indices of the top 2 columns

var othersViewColumns = [0].concat(top2);

othersViewColumns.push({

    type: 'number',

    label: 'Others',

    calc: function (dt, row) {

        var total = 0;
        for (var i = 1; i < dt.getNumberOfColumns(); i++) {
            total += (top2.indexOf(i) == -1) ? dt.getValue(row, i) : 0;
        }

        return total;

    }

});

var othersView = new google.visualization.DataView(pivotedData);

othersView.setColumns(othersViewColumns);

[Guy Forssman]

Hi There,

I would like to combine this pivoted table and a categoryfilter to created a Table and Annotation chart.

The categroyfilter is needed to let player choose themself and compare to others but not all (50+ ) players (columns )

I would obtain if this would work a nice scoreboard. 

I guess the first thing I need to do is put the pivoted data back in a DataTable or DataView but frankly I don't have a clue on how to achieve this.

Can you help me out?

[Hisham Alkilani]

Dear Andrew,

Could You please help me i have the same problem but i need to do it dynamically throw mysql, php, JSON how can i achieve this ? 

Please i need your help so bad contact me at this : aty...@yahoo.com 

Thanks in advance,

BR,

Hisham