Sorting a Table

[FlatCat]

I present my users with a large table control (about 30 columns x 100 rows) that contains alphanumeric data in some columns (such as sample ID's), a date/time in another, and various floating point values in other columns. My users would like to be able to click on a column header and have the table sorted on that column. Very standard behavior.

Sort 2D Array won't work here, I think, especially for numeric values in text format. It seems to me that I have to know what kind of data is in the selected column, convert it to doubles if necessary, sort the 1D array, and then reorder the table rows accordingly, either by keeping track of the sort order as I sort, or searching for each sorted item in the original array. 

That's all pretty straightforward, but a bit laborious. Not to mention the problem that LV will sort A12 before A3 in an alphanumeric sort, which is not what I'd like. I'm told that .NET controls will do some of this work automatically, but failing that is there a standard way of approaching this? It seems like a problem that would be encountered pretty frequently.

[altenbach]

---

@FlatCat wrote:

That's all pretty straightforward, but a bit laborious. Not to mention the problem that LV will sort A12 before A3 in an alphanumeric sort, which is not what I'd like.

---

In a better designed system, mixed entries should have the numeric part padded with a sufficient amount of zeroes, then e.g. A003 would naturally sort before A012 (numeric=alpha sorting).

Sorting a 2D array by one particular column is easy once you know how to  sort that column (alpha or numeric) by taking advantage of the fact that clusters are sorted in cluster order. There are plenty of exampled here in the forum. No need for any "searching".

Simple example:

[FlatCat]

That makes sense. 

That doesn't solve the A22 A3 problem, but it's a great start. Thanks.

[altenbach]

See my picture above.

As I said, your A22 A3 problem is a design flaw. Maybe you can fix the program that generates the entries, using a format that pads with zeroes if needed. I would be difficult to come up with a general set of rules for mixed sorting (...even though windows explorer does a reasonable job). What if a column is in roman numerals? 😄

If you want date/time sorted properly, it should be in padded to fixed fields and arranged in magnitude, e.g. YYYY/MM/DD, HH:MM:SS (using 24 hour format), else you need to know exactly how it is formatted and convert to time.

[FlatCat]

I am not in control of the IDs that customers give their samples. Everyone uses a different schema, with various mixes of letters and numbers. But your example will get me a long way. 

[raphschru]

Hi,

You can use what some people called "alphalogical" sorting:

https://forums.ni.com/t5/Example-Code/Alphabetical-and-Numerically-Logical-String-Array-Sort/ta-p/39...

Regards,

Raphaël.

[altenbach]

---

@raphschru wrote:

You can use what some people called "alphalogical" sorting:

https://forums.ni.com/t5/Example-Code/Alphabetical-and-Numerically-Logical-String-Array-Sort/ta-p/39...

---

Still does not solve all cases, e.g. how would you sort if the number is in exponential format:  (e.g. A1.2E5, A2.5E6, A5.8E2) or SI units.

If it is just alpha characters and integers, things are simpler.

[FlatCat]

That will be good enough for my purposes, I think. I won't have complex cases like exponential numbers or numbers with units. 

Thanks. 

David

[jjohn1]

For the A22 A3 problem look up a "natural sort".  I have used it in Python when I had an array of file names that did not have padded numbers.

For sorting multiple arrays of related data, I use a keysort.  This creates an array of ascending numbers (the key) and sorts the key based upon values in the selected array.  The sorted key array can be used as indexes to rearrange the related data arrays.  The algorithm can be found in the Numerical Recipes text.

[altenbach]

Instead of using ancient code that has many places for bugs to hide, I would just get the sort key by using a simple subVI that expands adjacent numeric digits to zero padded integers of sufficient width so alpha sorting=numeric sorting.

Here is a quick draft (It also makes the letter fields case insensitive, but that can be changed if needed).:

See if it works for you. (As I said, this is a very quick draft and needs testing!)