Sorting by multiple values

Hi!

I was working on my doc when I got stuck - I can’t come up with any way to sort rows by multiple values.
Unfortunately, this isn’t as easy as it seems as there are some other limitations:

  • I need to be able to detect which row is “higher” after the sorting, something like Row1.Sort > Row2.Sort
  • Those values are numbers with unknown length (and a date, but this is also technically a number), so I can’t just do Val1*1000+Val2*10+Val3
  • This is intended for a table with ~1000 of rows (and very often some rows are added and some are deleted), so it needs to be fast
  • Those numbers can be very big - up to 10^10
  • Some of those numbers should be sorted in the ascending order, and other in descending order
  • Those numbers don’t have to be integers, they may be fractions and they shouldn’t be rounded or the order may be incorrect

Earlier this could be done by just combining those value with spaces and multiplying selected ones by -1, but in recent coda updates this has been changed and is no longer functional.

And here are some of my failed attempts at achieving this:

I would be very, very grateful for any kind of help with this.

Hello @Filmos!

Just trying to think through this with you, I get why this method would be a bit shaky, but if you were to instead to multiply and divide, and seperate the two sorts by enough decimals that they would not interact with one another (presuming you don’t have excessively small multiples - I guess your range is really important here), would this work?:

1 Like

You are right that there actually is a limit to the value precision. I got so fixated about them not being integers that I overlooked that they can be easily turned into integers.
And while your solution would work fine if there are just two variables, there are about 5 variables in my case.

I did some calculations based on my current max ranges for the those variables, and numbers combined in this method would be around 10^39. And this may even increase if the doc gets more complex.

Unfortunately, this is no longer considered a “number”, but a “big num” and coda doesn’t seem to be able to handle those:
image
image

1 Like

Ah yes, makes sense!

If you check back with my document, you’ll see I also made an attempt with LeftPad, which shows some promise, but I ran into some bizarre things there based on the difference of Coda’s sorting methods, take a look at the test sort table as well to see of the strange ways items are interpreted for sorting…

Good luck as you continue your work!

1 Like

Thanks to @Bobby_Ritter help, I was able to came up with a pretty good solution.

I’m converting the number values from base 10 to base 20000, which allows me to store integers up to 1.6e17, or numbers up to 1.6e13 with 0.0001 precision.
Then I’m converting those numbers into unicode symbols from 20000 to 40000 and into a string.
This way the entries can be sorted by the lexicographical order of the strings.

This solution only relies on turning the numbers into a string, which is just a bunch of mathematical operations so it doesn’t require a lot of computing power.
I also did some actual performance testing, and everything works perfectly and very fast.

The final formula looks like this:

List(
  thisRow.[First number],
  thisRow.[Second number]
).FormulaMap(
   Character(Floor(CurrentValue/800000000)+20000)+
   Character(Floor(CurrentValue/40000%20000)+20000)+
   Character(Floor(CurrentValue/2%20000)+20000)+
   Character(Floor(CurrentValue*10000%20000)+20000)
).Join(" ")

And produces following results:
image

3 Likes

Haha this is ridiculously impressive!

Does it cover this requirement, though?

Since there aren’t any negative numbers, if you want to invert order you can just use 1.6e13-n.
A little bit ugly, but it works.