Sorting with signed numbers and percentage signs

tsiqueira

I'm trying to use Datatable to sort a column that has percentages that are also signed numbers. You can see the live example at: http://live.datatables.net/evuwut

I've tried using the "Fully signed numbers sorting" together with the "Percentage" plugin but have not been able to get it working.

I have been able to get the percentage and enum plugins to work separately in other columns. However, i can't get both the signed and percentage plugins to work together on the same column.

Any tips on how to get those two plugins to work together?

Thank you,

TS

fbas

I guess the sType detection doesn't work here and even setting sType to numeric did not work,

but a simple addition of some custom sorting routines fixes it right up

see test case at http://live.datatables.net/akuwaz/4/edit
[code]
$(document).ready(function() {

// custom oSort routines need both an ascending and descending sort function
jQuery.fn.dataTableExt.oSort['pct-asc'] = function(x,y) {
x = parseFloat(x);
y = parseFloat(y);

return ((x < y) ? -1 : ((x > y) ? 1 : 0));
};

jQuery.fn.dataTableExt.oSort['pct-desc'] = function(x,y) {
x = parseFloat(x);
y = parseFloat(y);

return ((x < y) ? 1 : ((x > y) ? -1 : 0));
};

// we specify our "pct" sorting type for column 0
$('#example').dataTable( {
"aoColumnDefs": [
{ "sType": "pct", "aTargets": [ 0 ] }
]
});
} );
[/code]

source: http://www.datatables.net/development/sorting#type_based

tsiqueira

Thanks for your help but it's still not working. In addition, it is not sorting simple numeric columns properly. I'm going to have to look for an alternative solution.

allan

fbas's solution looks good to me and the example posted sorts as expect (thanks for taking the time to provide such a details solution fbas).

@tsiqueira - Please link to attest cast which shows it not working. Also I would say that you can only apply one sorting plug-in to a column - two would conflict (one might have different sorting from another).

Allan

tsiqueira

For example, http://live.datatables.net/uvevij

If you sort it by different columns then sort the browser column, you'll see that the empty slot in the Browser column does not move or sort with the rest of the rows. What i am trying to do is to get it to just push whatever cell that is empty to the end/bottom of the column no matter if its asc or desc.

In addition, for some reason, the fix by fbas works on the testing environment (live.datatables.net) but does not work on my environment. The column does not sort, I click on it and it does nothing, almost like there is no response from the javascript. Unfortunately, there's no javascript error, so i can't tell where the problem is yet. I might have to breakpoint it and try to debug it.

The only sorting working right now in my environment is the string sorting which is by default, and the num-html sorting plugin i used for a column that has numbers but the values are within html tags.

fbas

that would be a weakness in the parseFloat() routine. it does not return a valid number when given non-numeric inputs.

adding some slightly more complicated logic will convert non-numeric values to 0 (or whatever value you want..)

http://live.datatables.net/uvevij/2/edit#preview
[code]
x = parseFloat(x);
y = parseFloat(y);

if (isNaN(x)) x = -9000;
if (isNaN(y)) y = -9000;

return ((x < y) ? -1 : ((x > y) ? 1 : 0));
[/code]

I wonder if there are some browser-specific issues as well going on..? what's your environment?

tsiqueira

That works on the testing environment, thanks, that's the logic I need. I did something similar for the enum in order to keep the empty cells at the bottom. I will find the place in the code that takes care of the strings and do the same for that.

I have to figure out what's going on with my table and why it's not working there. I'm developing in VS2012, and using Chrome to look at the page. I doubt it's the browser, we use plenty of other javascript around and it works. Maybe i'm doing something funky on my end.

tsiqueira

Alright, so i got it working on my environment. The issue with the last two columns not working was that there was more complexity than i remembered. The column has signed integers (+/-), a percentage sign, AND is wrapped in HTML. So i used your fix for the signed integers and for the blank spaces, and had to add the num-html plugin to it as well.

Thanks for the help fbas, I appreciate it. Allan (and others involved in dev), i hope you guys continue to improve this plugin. It still needs work, but it is the easiest to modify from any of the other options available that i found, and the one that covers the most use cases i could think of. Thanks again. Here's the final plugin code i ended up using:

[code]
$(document).ready(function () {
jQuery.fn.dataTableExt.oSort['pct-asc'] = function (x, y) {
var a = String(x).replace(/<[\s\S]*?>/g, "");
var b = String(y).replace(/<[\s\S]*?>/g, "");

a = parseFloat(a);
b = parseFloat(b);

if (isNaN(a)) a = 9000;
if (isNaN(b)) b = 9000;

return ((a < b) ? -1 : ((a > b) ? 1 : 0));
};

jQuery.fn.dataTableExt.oSort['pct-desc'] = function (x, y) {
var a = String(x).replace(/<[\s\S]*?>/g, "");
var b = String(y).replace(/<[\s\S]*?>/g, "");

a = parseFloat(a);
b = parseFloat(b);

if (isNaN(a)) a = -9000;
if (isNaN(b)) b = -9000;

return ((a < b) ? 1 : ((a > b) ? -1 : 0));
};

jQuery.extend(jQuery.fn.dataTableExt.oSort, {
"num-html-pre": function (a) {
var x = String(a).replace(/<[\s\S]*?>/g, "");
return parseFloat(x);
},

"num-html-asc": function (a, b) {
return ((a < b) ? -1 : ((a > b) ? 1 : 0));
},

"num-html-desc": function (a, b) {
return ((a < b) ? 1 : ((a > b) ? -1 : 0));
}
});

jQuery.extend(jQuery.fn.dataTableExt.oSort, {
"enum-pre": function (a) {
// Add / alter the switch statement below to match your enum list
switch (a) {
case "A": return 1;
case "B": return 2;
case "C": return 3;
case "D": return 4;
default: return 5;
}
},

"enum-asc": function (a, b) {
if (a === 5) a = 90;
if (b === 5) b = 90;
return ((a < b) ? -1 : ((a > b) ? 1 : 0));
},

"enum-desc": function (a, b) {
if (a === 5) a = -90;
if (b === 5) b = -90;
return ((a < b) ? 1 : ((a > b) ? -1 : 0));
}
});

$('#example').dataTable({
"aaSorting": [[1, 'asc']],
"bPaginate": false,
"bLengthChange": false,
"bFilter": false,
"bInfo": false,
"bAutoWidth": false,
"aoColumnDefs": [
{ "bSortable": false, "aTargets": [0] },
{ "sType": "num-html", "aTargets": [2] },
{ "sType": "pct", "aTargets": [3] },
{ "sType": "pct", "aTargets": [4] },
{ "sType": "pct", "aTargets": [5] },
{ "bSortable": false, "aTargets": [6] },
{ "sType": "enum", "aTargets": [7] },
{ "sType": "pct", "aTargets": [8] },
{ "sType": "pct", "aTargets": [9] }
]
});
});
[/code]

allan

Good to hear you got it working.

Allan