Sorting results after binning


I have an input file with a column where I create 8 Bins with equal frequency and use Borders as Bin naming, see forum_question.knwf (96.6 KB)
Now I want to sort the bins in a readable order like this, e.g. to visualise it in a Bar Chart. But the sorting sees the comma, as a decimal-seperator, where I expect a dot of nothing (because the represents thousands)
After sorting the order looks like this

This is what I need (with or without the dot as a thousand seperator).

Any idea’s where to look what to do? Replace the comma in the middle by something else? But what if there are 2/4/6 comma’s.

Tnx Hans

Hi @HansS -

Like this?

I ended up binning twice - once using borders and once using numbered for the bins. Then joined the results by rowID, sorted on the binned version, and plotted the bordered version.

It’s hacky, but maybe it works for you?

2022-04-06 10_17_23-KNIME Analytics Platform
forum_question_SF.knwf (152.5 KB)


Unfortunately, the Auto-Binner has no option to choose a separator (feature request @ScottF? :wink:), so it produces 1 to 3 commas. Since your input is integers only I changed the default setting to 0 decimals. Then I extracted the limits of each interval and sorted by those 2 new columns.

Not sure if I like Scott’s solution more though :sweat_smile:

Since this will only work with integers, I can try to come up with some other replacement trick to get rid of the commas if neither solution works for you.

edit: I “fixed” it by doing more sophisitcated replacing, so that it works with decimals as well, but I realised that my logic will break for numbers bigger than 1 million. Guess I’ll stop experimenting and actually do that feature request :sweat_smile:
sorting results after binning
sorting results after binning.knwf (138.0 KB)

1 Like

Hi @ScottF

Tnx for replying. A creative solution (need some extra steps when there are >9 Bins) . I used the barchart as an example of the “clumsy” sort order. I need the numbers from binning mulitple columns in a datatable as (part of) my input for a Tableau dashboard.

Hi @Thyme

I think your directions may lead to a solution. I don’t expect to encounter numbers bigger than 1 million, and as far as I know there are no decimals in the data…?! So I’ll give it a try,
But it would be so much easier if the Auto-Binner node has an option to choose a seperator (@ScottF ).

If you only have integers, then using the advanced formatting options is the way to go in your case. For sorting the bins, I think Scotts solution is more robust.

For future people:
I collected all 3 methods into a single WF and added a String Manipulator to Scott’s WF so it can sort more than 9 bins.

sorting results after binning.knwf (362.2 KB)


This topic was automatically closed 7 days after the last reply. New replies are no longer allowed.