sorting currency with NA

patchlab

Hi, I am able to sort the currency column using the html-num-fmt type. but if there is a row that has the NA value in that column, the sorting will not work correctly, it just sort by text instead of number. How can I do something to ignore that NA row from sorting?

allan

This is correct - you'd need to modify the plug-in to detect the NA text and set that to be 0 or -Infinity in the sort order.

Allan

patchlab

can you show me how to modify the plug-in? this is the code I have so far

$(document).ready(function() {


if($("html:lang(en)").length){
decimal = '.';
thousands = ',';
coldef = "{orderable: false, targets:'no-sort'},{ targets: 4, type: 'html-num-fmt' }";
}else{
decimal = ',';
thousands = '.';
coldef = "{orderable: false, targets:'no-sort'}";
}
$('#table').DataTable({

"paging": false,
"info": false,
language: {
'decimal': decimal,
'thousands': thousands
},
columnDefs: [coldef]
});
});

colin

Hi @patchlab ,

You may be able to use columns.render to change the value being passed to the sort routine from NA to an empty string, something like

columnDefs: [{
  targets:4,
  "render": function ( data, type, row, meta ) {
      return type === 'sort' && data === 'NA'?  '' : data;
  }
}]

It might be worth a try

Cheers,

Colin

patchlab

@colin I tried it and add console.log there, but doesn't seem it is running that function.

  "render": function ( data, type, row, meta ) {
console.log('here');
      return type === 'sort' && data === 'NA'?  '' : data;
  }

@allan do you mean the below code to modify the plugin?how do I know this code is running? the console.log doesn't seem to be working here.

$.fn.dataTable.ext.type.order['html-num-fmt-pre'] = function ( data ) {
    var units = data;
console.log('here');
    if ( $(units).text().replace(/(\r\n\t|\n|\r\t)/gm,"") == "NA" ) {
        
     return 0;
    }
 
};

allan

do you mean the below code to modify the plugin?how do I know this code is running?

Yes. If that code isn't running, can you link to a page showing the issue please? Are you using server-side processing?

Allan

patchlab

@allan
I put the code here now, https://jsfiddle.net/vf6Ljt0k/3/ can you see how I can edit, if you remove the NA TR row, then it sort ok, but with that it is not.

patchlab

@allan here is the correct link https://jsfiddle.net/vf6Ljt0k/3/

allan

Try this: https://jsfiddle.net/vf6Ljt0k/14/

There are a few problems with your example - the column definations were being given as a string and you weren't stripping the HTML from the cell data or taking account of the comma decimal. I've stripped it right back to the basics there to show how it can work.

Allan

patchlab

got it, thanks you