(Solved) How to sort datasheet (x-dim array) by a few columns (at once in 1 loop) ?

General FreeBASIC programming questions.
ppf
Posts: 88
Joined: Oct 10, 2017 6:41

(Solved) How to sort datasheet (x-dim array) by a few columns (at once in 1 loop) ?

Postby ppf » Nov 29, 2019 17:16

Hi,

my question is, howto do this in FB. When I am doing datasheet sort by 3 columns in Office, it's quite fast.
But I have no idea, howto do it 'FB smart'.
Ordinary solution is clear, assuming also slow.My interest is about some 'dependant sorting algo' ? Multisort ??
Could somebody to help me, please?
Say, the first column is year, second is day, third is hour, the rest are raw data (mixed types).Datasheet can be taken as one multidim UDT array, or a few vectors, for simplicity.Big datasheet FB sort by 1 column is working perfectly for me - note.
Last edited by ppf on Dec 07, 2019 13:10, edited 2 times in total.
dodicat
Posts: 6644
Joined: Jan 10, 2006 20:30
Location: Scotland

Re: How to sort datasheet (x-dim array) by a few columns ?

Postby dodicat » Nov 29, 2019 18:05

If you use a udt array holding the information then quicksort would do.
You must set each sort, give it a name, direction and field.

Code: Select all

 


#define up <,>
#define down >,<
#macro SetQsort(datatype,fname,b1,b2,dot)
    Sub fname(array() As datatype,begin As Long,Finish As Long)
    Dim As Long i=begin,j=finish
    Dim As datatype x =array(((I+J)\2))
    While  I <= J
        While array(I)dot b1 X dot:I+=1:Wend
        While array(J)dot b2 X dot:J-=1:Wend
                If I<=J Then Swap array(I),array(J): I+=1:J-=1
    Wend
    If J > begin Then fname(array(),begin,J)
    If I < Finish Then fname(array(),I,Finish)
    End Sub
#endmacro

Type udt
    As Long year
    As Long Day
    As Long Hour
    As String Name
End Type

'set up a sort for each required field
SetQsort(udt,Qsortyear,up,.year)
SetQsort(udt,Qsortday,up,.day)
SetQsort(udt,Qsorthour,up,.hour)
SetQsort(udt,Qsortname,down,.name)

Sub Fill(x() As udt)
    Dim As String s(1 To 5)={"Peter","Paul","Mary","Jim","Jean"}
    Randomize 1
    For n As Long=1 To Ubound(x)
    With x(n)
        .year=2000+Rnd*30
        .day=1+Rnd*7
        .hour=Rnd*24
        .name=s(Int(1+Rnd*5))
    End With
Next
End Sub


Sub printout(x() As udt,msg As String="")
    Print Ucase(msg)
     Print "year";Tab(10);"day";Tab(20);"hour";Tab(30);"name"
     Print
    For n As Long=1 To Ubound(x)
  Print x(n).year;Tab(10);x(n).day;Tab(20);x(n).hour;Tab(30);x(n).name     
Next
Print "Press a key . . ."
Print
Sleep
End Sub

Dim As udt x(1 To 20)

Fill(x())
printout(x(),"raw array")

qsortyear(x(),Lbound(x),Ubound(x))
printout(x(),"Sort by year")

qsortday(x(),Lbound(x),Ubound(x))
printout(x(),"Sort by day")

qsorthour(x(),Lbound(x),Ubound(x))
printout(x(),"Sort by hour")

qsortname(x(),Lbound(x),Ubound(x))
printout(x(),"Sort by name (downwards)")

print
print "Done"
sleep



 
badidea
Posts: 2122
Joined: May 24, 2007 22:10
Location: The Netherlands

Re: How to sort datasheet (x-dim array) by a few columns ?

Postby badidea » Nov 29, 2019 18:11

dodicat is way too fast. I was trying to make something similar.
ppf
Posts: 88
Joined: Oct 10, 2017 6:41

Re: How to sort datasheet (x-dim array) by a few columns ?

Postby ppf » Nov 29, 2019 23:09

Hi, dodicat,

really thanks for quick reply and impressive example !
(Your usage of macros is simply magic for me.)

But what is right syntax for multisort of array in your example, say "year and (then) hour" ? I don't see it..
My try tests give me couple of errors.
Trying this, e.g.

Code: Select all

qsortyearhour(x(),Lbound(x),Ubound(x))
printout(x(),"Sort by year andalso hour")


(My mind is fighting with your code, as I use slightly different way for real huge UDT array (1 column sort) in my app.
There I do no swaps of udt, whole array is constant all the time.I do temporary copy of column to sort, then sorted taggedly with its indexes - sortedID(i).
(Printed) Result is not simple indexing - a(i), but double indexing - a(sortedID(i)).
ppf
Posts: 88
Joined: Oct 10, 2017 6:41

Re: How to sort datasheet (x-dim array) by a few columns (at once in 1 loop) ?

Postby ppf » Nov 30, 2019 2:21

Seeing & compare on my old app and your example.Principially code very similar, nice.
My 3 paged control loop needs face lifting ;)) to 3 lines loop.Yeah.

Thinking from end to beginning on paper; to get procedure prototypes.

It could looks like this (pseudocode, procedure names first shot):

Code: Select all

   'types, macros, procedures
Type udt ...
macro SetQsort(datatype,fname,b1,b2,dot) ...
Sub Fill(x() As udt) ...
Sub printout(x() As udt,msg As String="") ...
Sub qSortMulti() ...
Sub qSortTagged() ...
   'main
Dim As udt x(1 To 10)
Fill(x())
   'set default flags
breakSorts=0
inkey=""
   'control loop
while not breakSorts            'flag to exit from all sorts
  if inkey/multikey .. then                            'change/select sort/multisort or set exit flag
   'selectSortCols()                     'set sort criteria - by which cols and order
   'makeMultiSort()                           'call qsortMulti()
   selectSortCols(setOrderPlusCols())                     'set sort criteria - by which cols and order
   makeMultiSort(setOrderPlusCols())                    'call qsortMulti()
   printout(x(),"Sort by : ",setOrderPlusCols())                    'print datasheet
  end if
wend
? "Sorts breaked, ending.."
sleep
end


Note - assuming multisort "Sort by year+day" will has different result then "Sort by day+year", IIRC, by my app.
jj2007
Posts: 1625
Joined: Oct 23, 2016 15:28
Location: Roma, Italia
Contact:

Re: How to sort datasheet (x-dim array) by a few columns ?

Postby jj2007 » Nov 30, 2019 10:49

dodicat wrote:If you use a udt array holding the information then quicksort would do.
You must set each sort, give it a name, direction and field

OP wants to sort a spreadsheet first by column C, then by column A, then by column B, etc., as does Excel. I am afraid QuickSort is not the right one for that task - you need a stable sort, like MergeSort.
ppf
Posts: 88
Joined: Oct 10, 2017 6:41

Re: How to sort datasheet (x-dim array) by a few columns (at once in 1 loop) ?

Postby ppf » Nov 30, 2019 12:30

Exactly. For huge array I use CombSort and MergeSort. For example here it is not important, I think, which kind of sort algo is used for demonstration of solution, is'n it ?
dodicat
Posts: 6644
Joined: Jan 10, 2006 20:30
Location: Scotland

Re: How to sort datasheet (x-dim array) by a few columns (at once in 1 loop) ?

Postby dodicat » Nov 30, 2019 13:00

Thanks jj2007.
Still using quicksort, you can adjust slightly to sort columns one by one, but still storing the data in a udt.
(Simply by swapping only the field)

Code: Select all



#define up <,>
#define down >,<
#macro SetQsort(datatype,fname,b1,b2,dot)
    Sub fname(array() As datatype,begin As Long,Finish As Long)
    Dim As Long i=begin,j=finish
    Dim As datatype x =array(((I+J)\2))
    While  I <= J
        While array(I)dot b1 X dot:I+=1:Wend
        While array(J)dot b2 X dot:J-=1:Wend
                If I<=J Then Swap array(I)dot,array(J)dot: I+=1:J-=1  'swap the field only  -- only change to previous code
    Wend
    If J > begin Then fname(array(),begin,J)
    If I < Finish Then fname(array(),I,Finish)
    End Sub
#endmacro

Type udt
    As Long year
    As Long Day
    As Long Hour
    As String Name
End Type

'set up a sort for each required field
SetQsort(udt,Qsortyear,up,.year)
SetQsort(udt,Qsortday,up,.day)
SetQsort(udt,Qsorthour,up,.hour)
SetQsort(udt,Qsortname,down,.name)

Sub Fill(x() As udt)
    Dim As String s(1 To 5)={"Peter","Paul","Mary","Jim","Jean"}
    Randomize 1
    For n As Long=1 To Ubound(x)
    With x(n)
        .year=2000+Rnd*30
        .day=1+Rnd*7
        .hour=Rnd*24
        .name=s(Int(1+Rnd*5))
    End With
Next
End Sub


Sub printout(x() As udt,msg As String="")
    Print Ucase(msg)
     Print "year";Tab(10);"day";Tab(20);"hour";Tab(30);"name"
     Print
    For n As Long=1 To Ubound(x)
  Print x(n).year;Tab(10);x(n).day;Tab(20);x(n).hour;Tab(30);x(n).name     
Next
Print "Press a key . . ."
Print
Sleep
End Sub

Dim As udt x(1 To 20)

Fill(x())
printout(x(),"raw array")

qsortyear(x(),Lbound(x),Ubound(x))
printout(x(),"Sort by year")

qsortday(x(),Lbound(x),Ubound(x))
printout(x(),"Sort by day")

qsorthour(x(),Lbound(x),Ubound(x))
printout(x(),"Sort by hour")

qsortname(x(),Lbound(x),Ubound(x))
printout(x(),"Sort by name (downwards)")

print
print "Done"
sleep

 

I have used quicksort for many years, found no problems so far.
However, you can create a suitable macro for a sort of your choice.
For example combsort (Which I don't like, and it is slower).

Code: Select all

 
#define up >
#define down <
#macro SetQsort(datatype,fname,dirn,dot)
    Sub fname(array() As datatype,begin As Long,Finish As Long)
            var size=Ubound(array),switch=0,j=0
            Dim As Single void=size
            Do
                void=void/1.3: If void<1 Then void=1
                switch=0
                For i As Integer =1 To size-void
                    j=i+void
                    If array(i)dot dirn array(j)dot Then
                        Swap array(i)dot,array(j)dot: switch=1
                    End If
                Next
            Loop Until  switch =0 And void=1
        End Sub
#endmacro

Type udt
    As Long year
    As Long Day
    As Long Hour
    As String Name
End Type

'set up a sort for each required field
SetQsort(udt,Qsortyear,up,.year)
SetQsort(udt,Qsortday,up,.day)
SetQsort(udt,Qsorthour,up,.hour)
SetQsort(udt,Qsortname,down,.name)

Sub Fill(x() As udt)
    Dim As String s(1 To 5)={"Peter","Paul","Mary","Jim","Jean"}
    Randomize 1
    For n As Long=1 To Ubound(x)
    With x(n)
        .year=2000+Rnd*30
        .day=1+Rnd*7
        .hour=Rnd*24
        .name=s(Int(1+Rnd*5))
    End With
Next
End Sub


Sub printout(x() As udt,msg As String="")
    Print Ucase(msg)
     Print "year";Tab(10);"day";Tab(20);"hour";Tab(30);"name"
     Print
    For n As Long=1 To Ubound(x)
  Print x(n).year;Tab(10);x(n).day;Tab(20);x(n).hour;Tab(30);x(n).name     
Next
Print "Press a key . . ."
Print
Sleep
End Sub

Dim As udt x(1 To 20)

Fill(x())
printout(x(),"raw array")

qsortyear(x(),Lbound(x),Ubound(x))
printout(x(),"Sort by year  combsort")

qsortday(x(),Lbound(x),Ubound(x))
printout(x(),"Sort by day combsort")

qsorthour(x(),Lbound(x),Ubound(x))
printout(x(),"Sort by hour combsort")

qsortname(x(),Lbound(x),Ubound(x))
printout(x(),"Sort by name (downwards) combsort")

print
print "Done"
sleep

 
dodicat
Posts: 6644
Joined: Jan 10, 2006 20:30
Location: Scotland

Re: How to sort datasheet (x-dim array) by a few columns (at once in 1 loop) ?

Postby dodicat » Nov 30, 2019 13:49

Here is another method using the tried and tested c runtime sort.
To sort the fields one by one the udt is made to hold array fields.
I have added another female name (gender equality)
Example.

Code: Select all

#include once "crt.bi"

'=========  set up c sort =========
#define up <,>
#define down >,<
#define ArrayToSort(x,start,finish) @X((start)),((finish)-(start)+1),Sizeof(X)
#macro SetCSort(Datatype,FnName,b1,b2,dot)
Function FnName Cdecl(n1 As Any Ptr,n2 As Any Ptr) As long
    If *Cptr(Datatype Ptr,n1)dot b1 *Cptr(Datatype Ptr,n2)dot Then Return -1
    If *Cptr(DataType Ptr,n1)dot b2 *Cptr(DataType Ptr,n2)dot Then Return 1
    return 0
End Function
#endmacro

Type udt
    As Long year(any)
    As Long Day(any)
    As Long Hour(any)
    As String Name(any)
End Type

Sub Fill(x As udt,max as long)
    Dim As String s(1 To 6)={"Peter","Paul","Mary","Jim","Jean","Anne"}
    Randomize 1
    For n As Long=1 To max
        x.year(n)=2000+Rnd*30
        x.day(n)=1+Rnd*7
        x.hour(n)=Rnd*24
        x.name(n)=s(Int(1+Rnd*6))
Next
End Sub


Sub printout(x As udt,msg As String="",max as long=20)
    Print Ucase(msg)
     Print "year";Tab(10);"day";Tab(20);"hour";Tab(30);"name"
     Print
    For n As Long=1 To max
  Print x.year(n);Tab(10);x.day(n);Tab(20);x.hour(n);Tab(30);x.name(n)     
Next
Print "Press a key . . ."
Print
Sleep
End Sub



dim as udt x
dim as long max=20 'number of entries/elements

redim x.year(1 to max)
redim x.day(1 to max)
redim x.hour(1 to max)
redim x.name(1 to max)

'set up a sort for each required field array (In reality, only one callback function is required for long)
SetCsort(long,Qsortyearcallback,up,)
SetCsort(long,Qsortdaycallback,up,)
SetCsort(long,Qsorthourcallback,up,)
SetCsort(string,Qsortnamecallback,down,)




Fill(x,max)
printout(x,"raw array")

qsort(arraytosort(x.year,Lbound(x.year),Ubound(x.year)),@Qsortyearcallback)
printout(x,"Sort by year",max)

qsort(arraytosort(x.day,Lbound(x.day),Ubound(x.day)),@Qsortdaycallback)
printout(x,"Sort by day",max)

qsort(arraytosort(x.hour,Lbound(x.hour),Ubound(x.hour)),@Qsorthourcallback)
printout(x,"Sort by hour",max)

qsort(arraytosort(x.name,Lbound(x.name),Ubound(x.name)),@Qsortnamecallback)
printout(x,"Sort by name (downwards)",max)

print
print "Done"
sleep



 
badidea
Posts: 2122
Joined: May 24, 2007 22:10
Location: The Netherlands

Re: How to sort datasheet (x-dim array) by a few columns (at once in 1 loop) ?

Postby badidea » Nov 30, 2019 22:46

This is what I made with qsort:

Code: Select all

#include "crt/stdlib.bi"

type row_type
   dim as ushort year_, day_, hour_
   dim as string bla1, bla2
   declare operator cast () as string
end type

operator row_type.cast () as string
   return str(year_) & ", " & str(day_) & ", " & str(hour_) & ", " & bla1 & ", " & bla2
end operator

function qSortCallback cdecl(pRow1 as row_type ptr, pRow2 as row_type ptr) as long
   if pRow1->year_ < pRow2->year_ then return -1
   if pRow1->year_ > pRow2->year_ then return +1
   'years equal
   if pRow1->day_ < pRow2->day_ then return -1
   if pRow1->day_ > pRow2->day_ then return +1
   'days equal
   if pRow1->hour_ < pRow2->hour_ then return -1
   if pRow1->hour_ > pRow2->hour_ then return +1
   'hours equal
   return 0
end function

const NUM_ROWS = 10000
dim shared as row_type row(NUM_ROWS - 1)

randomize timer
'fill with random data
for i as integer = 0 to ubound(row)
   with row(i)
      .year_ = int(rnd * 100) + 1950
      .day_ = int(rnd * 365) + 1
      .hour_ = int(rnd * 24)
      .bla1 = string(int(rnd * 10) + 1, int(rnd * 26) + asc("a"))
      .bla2 = string(int(rnd * 10) + 1, int(rnd * 26) + asc("A"))
   end with
next

print "Unsorted data:"
print "--- First 5 items ---"
for i as integer = 0 to 4
   print row(i)
next
print "--- Last 5 items ---"
for i as integer = NUM_ROWS - 5 to NUM_ROWS - 1
   print row(i)
next

qsort(@row(0), NUM_ROWS, sizeof(row_type), cptr(any ptr, @qSortCallback))

print !"\nSorted data:"
print "--- First 5 items ---"
for i as integer = 0 to 4
   print row(i)
next
print "--- Last 5 items ---"
for i as integer = NUM_ROWS - 5 to NUM_ROWS - 1
   print row(i)
next
badidea
Posts: 2122
Joined: May 24, 2007 22:10
Location: The Netherlands

Re: How to sort datasheet (x-dim array) by a few columns (at once in 1 loop) ?

Postby badidea » Nov 30, 2019 23:55

A bit experimental code that allows sorting order:

Code: Select all

#include "crt/stdlib.bi"

type row_type
   dim as ushort year_, day_, hour_
   dim as string bla1, bla2
   declare operator cast () as string
end type

operator row_type.cast () as string
   return str(year_) & ", " & str(day_) & ", " & str(hour_) & ", " & bla1 & ", " & bla2
end operator

enum E_DATA_ROW
   E_DATA_YEAR
   E_DATA_DAY
   E_DATA_HOUR
end enum

type data_type
   static as E_DATA_ROW sortOrder(0 to 2)
   dim as row_type row(any)
   declare constructor(numRows as integer)
   declare destructor()
   declare sub initRandom()
   declare sub printSome()
   declare sub sort(sort1st as E_DATA_ROW, sort2nd as E_DATA_ROW, sort3rd as E_DATA_ROW)
   declare static function qSortCallback cdecl(pRow1 as row_type ptr, pRow2 as row_type ptr) as long
end type
dim as E_DATA_ROW data_type.sortOrder(0 to 2)

constructor data_type(numRows as integer)
   redim row(numRows - 1)
end constructor

destructor data_type()
   erase row
end destructor

sub data_type.initRandom()
   for i as integer = 0 to ubound(row)
      with row(i)
         .year_ = int(rnd * 100) + 1950
         .day_ = int(rnd * 365) + 1
         .hour_ = int(rnd * 24)
         .bla1 = string(int(rnd * 10) + 1, int(rnd * 26) + asc("a"))
         .bla2 = string(int(rnd * 10) + 1, int(rnd * 26) + asc("A"))
      end with
   next
end sub

sub data_type.printSome()
   print "--- First 5 items ---"
   for i as integer = 0 to 4
      print row(i)
   next
   print "--- Last 5 items ---"
   for i as integer = ubound(row) - 4 to ubound(row)
      print row(i)
   next
end sub

sub data_type.sort(sort1st as E_DATA_ROW, sort2nd as E_DATA_ROW, sort3rd as E_DATA_ROW)
   sortOrder(0) = sort1st
   sortOrder(1) = sort2nd
   sortOrder(2) = sort3rd
   qsort(@row(0), ubound(row) + 1, sizeof(row_type), cptr(any ptr, @qSortCallback))
end sub

function data_type.qSortCallback cdecl(pRow1 as row_type ptr, pRow2 as row_type ptr) as long
   for i as integer = 0 to 2
      select case sortOrder(i)
      case E_DATA_YEAR
         if pRow1->year_ < pRow2->year_ then return -1
         if pRow1->year_ > pRow2->year_ then return +1
      case E_DATA_DAY
         if pRow1->day_ < pRow2->day_ then return -1
         if pRow1->day_ > pRow2->day_ then return +1
      case E_DATA_HOUR
         if pRow1->hour_ < pRow2->hour_ then return -1
         if pRow1->hour_ > pRow2->hour_ then return +1
      end select
   next
   return 0
end function

dim as data_type myData = data_type(10000)
randomize timer
myData.initRandom()

print "Unsorted data:"
myData.printSome()

print !"\nmyData.sort(YEAR, DAY, HOUR):"
myData.sort(E_DATA_YEAR, E_DATA_DAY, E_DATA_HOUR)
myData.printSome()

print !"\nmyData.sort(HOUR, DAY, YEAR):"
myData.sort(E_DATA_HOUR, E_DATA_DAY, E_DATA_YEAR)
myData.printSome()

Edit: Code updated, shared var removed / fixed.
Last edited by badidea on Dec 02, 2019 23:04, edited 1 time in total.
ppf
Posts: 88
Joined: Oct 10, 2017 6:41

Re: How to sort datasheet (x-dim array) by a few columns (at once in 1 loop) ?

Postby ppf » Dec 01, 2019 2:07

For web help just googled some VB forum; founded this code, plus other for sort in range.Looks like they use ordinary solution for multisort of datasheet, (when not used a built-in Excel functions) .

Code: Select all

rem URL=http://www.vbaexpress.com/forum/showthread.php?41938-Solved-sort-multi-dimensional-array&s=bcfeb8d8b80ebc815cbe73dfe36a81c9
Sub Sort_2D_Array()
     
    Dim v As Variant
    Dim i As Integer, j As Integer, ci As Integer
    Dim r As Integer, c As Integer
    Dim temp As Variant
    'Dim data(5, 2) As Variant
    Dim data() As Variant
   
    'populate array
    data = Sheet1.Range("inputarray")

    'Create 2-dimensional array
     
    'v = Array(56, 22, "xyz", 22, 30, "zyz", 56, 30, "zxz", 22, 30, "zxz", 10, 18, "zzz", 22, 18, "zxx")
    'For i = 0 To UBound(v)
    '    data(i \ 3, i Mod 3) = v(i)
    'Next
     
    'Bubble sort 1st column
     
    ci = LBound(data, 2)                         '1st column index
    For i = LBound(data) To UBound(data) - 1
        For j = i + 1 To UBound(data)
            If data(i, ci) < data(j, ci) Then
                For c = LBound(data, 2) To UBound(data, 2)
                    temp = data(i, c)
                    data(i, c) = data(j, c)
                    data(j, c) = temp
                Next
            End If
        Next
    Next
     
    'Bubble sort 2nd column, where adjacent rows in 1st column are equal
     
    ci = LBound(data, 2) + 1                     '2nd column index
    For i = LBound(data) To UBound(data) - 1
        For j = i + 1 To UBound(data)
            If data(i, ci - 1) = data(j, ci - 1) Then       'compare adjacent rows in 1st column
                If data(i, ci) < data(j, ci) Then
                    For c = LBound(data, 2) To UBound(data, 2)
                        temp = data(i, c)
                        data(i, c) = data(j, c)
                        data(j, c) = temp
                    Next
                End If
            End If
        Next
    Next
     
    'Output sorted array
     
    For r = LBound(data) To UBound(data)
        For c = LBound(data, 2) To UBound(data, 2)
            Debug.Print data(r, c);
        Next
        Debug.Print
    Next
     
End Sub

Anyway, this finally was an inspiration for a design of solution to test:
1/ first col is sorted by MergeSort algo (or other one with equal power)
2/ all next subsorts in/by selected cols in datasheet must be a matter of amount of adjacent equal values.
T.m., approximately for 2-5 equals a BubbleSort will be used ;
approx. for 6-1000 equals a QuickSort will be used ;
approx. for 1001-up equals a MergeSort will be used.
3/ all swaps will be on tagged index range, not array values.Only temporary copy of column will be sorted.
All the job will be about indexes.
dodicat
Posts: 6644
Joined: Jan 10, 2006 20:30
Location: Scotland

Re: How to sort datasheet (x-dim array) by a few columns (at once in 1 loop) ?

Postby dodicat » Dec 01, 2019 14:47

The cqsort is a good all round sort.
array size differences are catered for, it is not a standard recursive quicksort.
Anything included in msvcrt.dll is well tried and tested/optimised I would say.
The c code for qsort is here
https://code.woboq.org/userspace/glibc/stdlib/qsort.c.html

Here I use a matrix type representation of rows and columns.

Code: Select all




#include once "crt.bi"

'=========  set up c sort =========
#define up <,>
#define down >,<
#define ArrayToSort(x,start,finish) @X((start)),((finish)-(start)+1),Sizeof(X)
#macro SetCSort(Datatype,FnName,b1,b2,dot)
Function FnName Cdecl(n1 As Any Ptr,n2 As Any Ptr) As Long
    If *Cptr(Datatype Ptr,n1)dot b1 *Cptr(Datatype Ptr,n2)dot Then Return -1
    If *Cptr(DataType Ptr,n1)dot b2 *Cptr(DataType Ptr,n2)dot Then Return 1
    Return 0
End Function
#endmacro

#define range(f,l) int(Rnd*(((l)+1)-(f)))+(f)
'=================
SetCsort(Long,Qsortlongcallback,up,)
SetCsort(String,Qsortstringcallback,down,)
'================

randomize

Sub show overload(matrix() As Long,msg As String)
    Print msg
    For x As Long=Lbound(matrix,1) To Ubound(matrix,1)
        For y As Long=Lbound(matrix,2) To Ubound(matrix,2)
            Print matrix(x,y);
        Next
        Print
    Next
    Print
End Sub

Sub sort overload(matrix() As Long,i As Long)
    Dim As Long t(Lbound(matrix,1) To Ubound(matrix,1))
    For x As Long=Lbound(t) To Ubound(t)
        t(x)=matrix(x,i)
    Next
    qsort(arraytosort(t,Lbound(t,1),Ubound(t,1)),@Qsortlongcallback)
    For x As Long=Lbound(t) To Ubound(t)
        matrix(x,i)=t(x)
    Next
End Sub

Sub show overload(matrix() As string,msg As String)
    Print msg
    For x As Long=Lbound(matrix,1) To Ubound(matrix,1)
        For y As Long=Lbound(matrix,2) To Ubound(matrix,2)
            Print matrix(x,y);
        Next
        Print
    Next
    Print
End Sub

Sub sort overload(matrix() As string,i As Long)
    Dim As string t(Lbound(matrix,1) To Ubound(matrix,1))
    For x As Long=Lbound(t) To Ubound(t)
        t(x)=matrix(x,i)
    Next
    qsort(arraytosort(t,Lbound(t,1),Ubound(t,1)),@Qsortstringcallback)
    For x As Long=Lbound(t) To Ubound(t)
        matrix(x,i)=t(x)
    Next
End Sub




Dim As Long a(1 To 6,1 To 7)    '6 rows 7 columns
dim as string b(1 To 12,1 To 6) '12 rows 6 columns

'------fill up both randomly
For x As Long=1 To Ubound(a,1)
    For y As Long=1 To Ubound(a,2)
        a(x,y)=10+Rnd*20
    Next
Next

For x As Long=1 To Ubound(b,1)
    For y As Long=1 To Ubound(b,2)
        b(x,y)=chr(range(97,122),range(97,122),range(97,122),32)
    Next
Next
'--------------------

show(a(),"Original")

For n As Long=Lbound(a,2) To Ubound(a,2) 'loop through all columns
    sort(a(),n)
    show(a(),"column "+Str(n)+" sorted")
Next
print "Press a key . . ."
sleep

show(b(),"original")
For n As Long=Lbound(b,2) To Ubound(b,2) 'loop through all columns
    sort(b(),n)
    show(b(),"column "+Str(n)+" sorted down")
Next
print "Press a key to end . . ."
sleep


 
ppf
Posts: 88
Joined: Oct 10, 2017 6:41

Re: How to sort datasheet (x-dim array) by a few columns (at once in 1 loop) ?

Postby ppf » Dec 01, 2019 23:12

@dodicat

Do you have your own speed comparison of sort algos used by you in projects ?
I did nothing yet about that, btw just founded on rosetta code webpage a few freebasic sort algo sources.
E.g. Heapsort, Mergesort, Quicksort - by context marked as most powerful.

This one and your sort snippets, thanks a lot for it, really appreciated !!, leads me to some detailed test
about speed on my huge datasheet.
One another minor problem founded, but I'll fix it quickly, I think.
It's about quick key input and recognition of keys sequence to determine, which cols and order is passed into multisort routine.
Real data needs speed, so I want to avoid GUI dialogs, like Excel has for cols selection.
Say, single keypress 1,2,3,4,5,6 numpad represent six independent sorts by 1 column.This works well for me.
Now I must recognize Sequence keypresses as 'shortcut' for multisort (f.e. 32461), also some break press, when typo happened.
It must be sequence, not a triple/more keypress, I think - it's not suitable.
Sequential keypress in theat case easy solves possible typo by sending 'Delete buffer' flag, ideal would be Backspace key.
ppf
Posts: 88
Joined: Oct 10, 2017 6:41

Re: How to sort datasheet (x-dim array) by a few columns (at once in 1 loop) ?

Postby ppf » Dec 02, 2019 7:23

@ badidea

Now tried your method.Short code, looks smart, pointers,callback.. and works.I like it.
This design is behind my pointer horizon :-(

Then tried Excel intent.Any permutation/combination of sort criteria.
Have you tried this syntax ?

Code: Select all

print !"\nmyData.sort(HOUR):"
rem myData.sort(E_DATA_HOUR)      '...fail
myData.sort(E_DATA_HOUR,0,0)      '...works !
myData.printSome():sleep

print !"\nmyData.sort(DAY,HOUR):"
rem myData.sort(E_DATA_DAY, E_DATA_HOUR)      '...fail
myData.sort(E_DATA_DAY, E_DATA_HOUR,0)         '...works !
myData.printSome():sleep


Just in this, I would expect no sort, but it sorts by year, seems..

Code: Select all

print "Unsorted data :"
rem myData.sort()      '...fail
myData.sort(0,0,0)         '...works, but always sort by year !
myData.printSome():sleep


I'll expand to 5 sort cols on real data.
(My real maximum is cca 10 cols, and obviously used max 5 cols.
2-5 is normal to get correct preview about status quo).

Good job, thank you for such smart idea.Keep it forwarding !

Return to “General”

Who is online

Users browsing this forum: No registered users and 5 guests