What's it all about, Alfie?

I've been working on a new page in my web app - something that will display the latest data (or any point in time, actually) and the users wanted to have it sorted by a very unusual key - asset class, then region, then desk. It's nothing simple, and I was worrying about how I'd get this done in a clean way. I didn't want to implement another sorting scheme... and I didn't want to mess with the reorganization of the table. But I just wasn't seeing how I would be able to make this happen easily.

Then it came to me: the DataTable from Google (in JavaScript) and it's Java counter-part written by me, has the sort() method, and that could be used to organize the rows in any way I want if I provided it a suitable key. The trick was to give it that key.

If I classified the row labels into their asset class, region, and desk - by name, then I could use this to create the index I needed.

and then:

so that I can then (in JavaScript) map the portfolios to these regions and asset classes (same will go for the desks, but it's an unnecessary complication here):

  var assetClass = new Array();
  assetClass['One'] = 'Equities';
  assetClass['Two'] = 'Equities';
  assetClass['Three'] = 'Rates';
  assetClass['OneMore'] = 'Rates';
  assetClass['Huey'] = 'Commodities';
  assetClass['Louie'] = 'Commodities';
 
  var region = new Array();
  region['One'] = 'USA';
  region['Two'] = 'Europe';
  region['Three'] = 'USA';
  region['OneMore'] = 'USA';
  region['Huey'] = 'USA';
  region['Louie'] = 'Europe';

With all this static data established for each portfolio, I can then create the JavaScript function to sort a passed in table on a known column. Let's say for my page, the portfolio name is in the first column. I could look for it but the getColumnLabel() method on the DataTable, but I know it's there, and that saves a little time.

  function sortByAssetClassAndRegion(tbl) {
    // make a new column that we'll use to sort
    var col = tbl.addColumn('string');
    // populate it with the mapping of the names based on the attributes
    for (var row = 0; row < tbl.getNumberOfRows(); ++row) {
      var portfolio = tbl.getValue(row, 0);
      tbl.setValue(row, col, assetClass[port] + '-' + region[port] + '-' + port);
    }
    // now just sort by the new column
    tbl.sort([{ column: col }]);
    // delete it now as it's served it's purpose
    tbl.removeColumn(col);
  }

This is pretty nice in that it leaves the table sorted by this other key, but it's very quickly done. The key's composition can be changed in any number of ways to make this even more flexible. For example, I expanded on this to make a sorting order that was specifically set by the user - and had nothing to do with asset class or region.

First, make an array with the sorting order you want to have:

  var sortingOrder = new Array();
  var i = 0;
  sortingOrder['One'] = i++;
  sortingOrder['Two'] = i++;
  sortingOrder['Three'] = i++;
  sortingOrder['OneMore'] = i++;
  sortingOrder['Huey'] = i++;
  sortingOrder['Louie'] = i++;

then you can create the sorting function:

  function sortByDefinedOrder(tbl, order) {
    // make a new column that we'll use to sort
    var col = tbl.addColumn('number');
    // populate it with the mapping of the names based on the attributes
    for (var row = 0; row < tbl.getNumberOfRows(); ++row) {
      tbl.setValue(row, col, order[tbl.getValue(row, 0)]);
    }
    // now just sort by the new column
    tbl.sort([{ column: col }]);
    // delete it now as it's served it's purpose
    tbl.removeColumn(col);
  }

and then it can be called:

  sortByDefinedOrder(answerData, sortingOrder);

It's not rocket science, but it is a nice little tool to have in hand to make sorting easy.

This entry was posted on Tuesday, January 5th, 2010 at 2:16 pm and is filed under Coding, Cube Life. You can follow any responses to this entry through the RSS 2.0 feed. Both comments and pings are currently closed.