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
(0,82]
(1,015,1,638]
(1,638,1,826]
(210,430]
(430,690]
(690,1,015]
(82,210]
[0,0]
This is what I need (with or without the dot as a thousand seperator).
[0,0]
(0,82]
(82,210]
(210,430]
(430,690]
(690,1.015]
(1.015,1.638]
(1.638,1.826]
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.
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.
Unfortunately, the Auto-Binner has no option to choose a separator (feature request @ScottF? ), 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
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 sorting results after binning.knwf (138.0 KB)
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.
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.
), 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.
