|
Sorting
Aug 28, 2019 9:38:16 GMT -5
Post by mpranger on Aug 28, 2019 9:38:16 GMT -5
I'm stumped again. I searched for Liberty Basic sorting
routines and found a couple of examples. I am able to
sort single numbers OK. But when I do a multi dimensional sort
(I think that's what it is called)
it's only sorting on the first digit of the last number.
I have given it a lot of effort before asking for help
however I'm just not getting it. I'm sure it's something simple.
My data file "wid2.txt" contains:
" 1-",25,25,0,89
" 2-",29,29,0,58
" 3-",32,31,0,63
" 4-",37,64,0,101
I would like to sort the data on the last column to the right
to get a deciding order to produce this:
" 4-",37,64,0,101
" 1-",25,25,0,89
" 3-",32,31,0,63
" 2-",29,29,0,58
Can someone please show me how to do this before I lose my mind?
Thanks for any help.
Greg
|
|
|
Sorting
Aug 28, 2019 11:52:08 GMT -5
Post by tenochtitlanuk on Aug 28, 2019 11:52:08 GMT -5
Your data is a mixture of strings and numbers, so will be held in a string array. In my example below I read the data from data statements- I was too lazy to save as a file! The LB sort is alphabetic on first character, so the trick is to pad the numbers all to the same length.
dim myArray4( 10, 10)
data " 1-", 25, 25, 0, 89 data " 2-", 29, 29, 0, 58 data " 3-", 32, 31, 0, 63 data " 4-", 37, 64, 0, 101
for i =1 to 4 for j =1 to 5 read a$ myArray$( i, j) =a$ next j next i
print "Data as read into array"
for i =1 to 4 for j =1 to 5 print myArray$( i, j), next j print "" next i
print "": print "Now sorting alphabetically on 5th column"
sort myArray$(), 1, 4, 5 ' rows 1 to 5 sorted on column 5
for i =1 to 4 for j =1 to 5 print myArray$( i, j), next j print "" next i
print "NOT what you wanted!"
print "" print "Let's replace the last column with leading zeroes to pad to the same length"
for row =1 to 4 tmp$ =myArray$( row, 5) tmp$ =right$( "0000" +tmp$, 4) myArray$( row, 5) =tmp$ next row
print "Data as now held in the array"
for i =1 to 4 for j =1 to 5 print myArray$( i, j), next j print "" next i
print "": print "Now sorting alphabetically on 5th column"
sort myArray$(), 1, 4, 5 ' rows 1 to 5 sorted on column 5
for i =1 to 4 for j =1 to 5 print myArray$( i, j), next j print "" next i
print "": print "What you wanted??? Whoops, wrong order!"
sort myArray$(), 4, 1, 5 ' rows 4 to 1 sorted on column 5
for i =1 to 4 for j =1 to 5 print myArray$( i, j), next j print "" next i
print "Yeah!!"
end
Producing.. Data as read into array 1- 25 25 0 89 2- 29 29 0 58 3- 32 31 0 63 4- 37 64 0 101
Now sorting alphabetically on 5th column 4- 37 64 0 101 2- 29 29 0 58 3- 32 31 0 63 1- 25 25 0 89 NOT what you wanted!
Let's replace the last column with leading zeroes to pad to the same length Data as now held in the array 4- 37 64 0 0101 2- 29 29 0 0058 3- 32 31 0 0063 1- 25 25 0 0089
Now sorting alphabetically on 5th column 2- 29 29 0 0058 3- 32 31 0 0063 1- 25 25 0 0089 4- 37 64 0 0101
What you wanted??? Whoops, wrong order! 4- 37 64 0 0101 1- 25 25 0 0089 3- 32 31 0 0063 2- 29 29 0 0058 Yeah!!
|
|
|
Sorting
Aug 28, 2019 13:03:50 GMT -5
Post by meerkat on Aug 28, 2019 13:03:50 GMT -5
Just for fun here is another alternative using a memory DB..
sqliteconnect #sql, ":memory:" sql$ = "CREATE TABLE sortData(a1,a2,a3,a4,a5)" #sql execute(sql$)
sql$ = "INSERT INTO sortData VALUES (-1,25,25,0, 89), (-2,29,29,0, 58), (-3,32,31,0, 63), (-4,37,64,0,101);" #sql execute(sql$)
sql$ = "SELECT * FROM sortData ORDER BY a5 desc,a1,a2,a3,a4" #sql execute(sql$) rows = #sql ROWCOUNT() 'Get the number of rows for i = 1 to rows #row = #sql #nextrow() a1 = #row a1() a2 = #row a2() a3 = #row a3() a4 = #row a4() a5 = #row a5() print i,a1,a2,a3,a4,a5 next i
|
|
Sver
Full Member
Posts: 145
|
Sorting
Aug 28, 2019 13:13:52 GMT -5
Post by Sver on Aug 28, 2019 13:13:52 GMT -5
About sorting.
Has somebody a function for stable sorting, when there are more columns ? Example, by second click hold the same ranking when there are same values in a other column.
|
|
|
Sorting
Aug 28, 2019 13:35:48 GMT -5
Post by Rod on Aug 28, 2019 13:35:48 GMT -5
The age old remedy for this is to create a “sorting column”. So you need to create an “index” column which is a concatenation of everything you want to sort on. yyyymmddname for example. Run through and create that column then sort on it.
|
|
|
Sorting
Aug 29, 2019 2:42:11 GMT -5
Post by metro on Aug 29, 2019 2:42:11 GMT -5
Just for fun here is another alternative using a memory DB.. meerkat's code is suitable for lb5 if you are contemplating using SQL sorting then this code should work with lb4.5.1
change this line CALLDLL #sq3, "SQ3_4_LB_GetRecordsetValueOfRow", "_" AS ptr,result AS long .....form underscore to comma or space to change delimiter
DIM LineItem$(1000): dim info$(10,10) : dim d$(2) ' global DB$,hDB GOSUB [setStruct] GOSUB [openDll]
hDB=createDB(":memory:")
SQL$ = "CREATE TABLE sortData(a1,a2,a3,a4,a5)" GOSUB [executeSQL] ' CREATE the TABLE
GOSUB [INSERTRECORDS] GOSUB [DISPLAYALL] close #sq3
WAIT
[LOADINFO] 'GET DISK DATA z=1 open "wid2.txt" for input as #inF WHILE eof(#inF)=0 line input #inF, LineItem$(z) z=z+1 WEND close #inF
RETURN
[INSERTRECORDS] row$(1) = "-1,25,25,0, 89" row$(2) = "-2,29,29,0, 58" row$(3) =" -3,32,31,0, 63" row$(4) = "-4,37,64,0,101"
for a = 1 to 4 row$(a) = "'" ; row$(a) ; "'" row$(a) = strRep$(row$(a),",","','")
SQL$ = "INSERT INTO sortData VALUES (" ; row$(a) ; ")" gosub [executeSQL] next RETURN
[DISPLAYALL] SQL$ = "SELECT * FROM sortData ORDER BY a5 desc,a1,a2,a3,a4"
calldll #sq3, "SQ3_4_LB_GetRecordset", SQL$ as ptr,hDB as long, RS as struct, result as long calldll #sq3, "SQ3_4_LB_GetLastMessage", result as long msg$ = Winstring(result)
IF instr(msg$,"Error") THEN notice msg$ END IF
IF RS.Rows.struct THEN PRINT str$(RS.Rows.struct); " Records Found and sorted by a5 : ":print
FOR n = 1 TO RS.Rows.struct CALLDLL #sq3, "SQ3_4_LB_RecordsetMoveToRow",n AS long, result AS void CALLDLL #sq3, "SQ3_4_LB_GetRecordsetValueOfRow", "_" AS ptr,result AS long
IF result THEN RecFound$= Winstring(RS.StrAdr.struct) print RecFound$ end if
CALLDLL #sq3, "SQ3_4_LB_RecordsetMoveNext", result AS void NEXT n END IF
CALLDLL #sq3, "SQ3_4_LB_ReleaseRecordset", result AS void RETURN
[executeSQL] calldll #sq3, "SQ3_4_LB_Execute",SQL$ as ptr, hDB as long,result as long IF result = 0 THEN calldll #sq3, "SQ3_4_LB_GetLastMessage", result as long print Winstring(result) END IF RETURN
[setStruct] Struct RS,_ BOF as long,_ ' is True when CurrPos = 1 EOF as long,_ ' is True when CurrPos = Rows Handle as long,_ ' address of recordset data returned by sqlite3.dll (dont't use) Rows as long,_ ' number of rows in recordset Cols as long,_ ' number of columns in recordset CurrPos as long,_ ' current row in recordset StrAdr as long ' address of data item (pointer to a string) RETURN
[openDll]
Open "SQ3_4_LB.dll" for DLL As #sq3 calldll #sq3, "SQ3_4_LB_GetLastMessage", result as long msg$ = Winstring(result) ' LastMessage would be "General Error - Couldn't open 'sqlite3.dll'" if instr(msg$,"Error") then ' If we have an error notice msg$ : close #sq3 ' close sqlite and end program end end if RETURN
[connectDB] calldll #sq3, "SQ3_4_LB_OpenDB",DB$ as ptr, overwrite as long, hDB as long ' see if it exist If hDB = 0 then calldll #sq3, "SQ3_4_LB_GetLastMessage", result as long connectDB$ = Winstring(result) notice connectDB$ end if RETURN
FUNCTION createDB(DB$) calldll #sq3, "SQ3_4_LB_CreateDB",DB$ as ptr, overwrite as long, hDB as ulong calldll #sq3, "SQ3_4_LB_GetLastMessage", result as long 'message for testing
If hDB = 0 then calldll #sq3, "SQ3_4_LB_GetLastMessage", result as long end if createDB=hDB END FUNCTION
FUNCTION strRep$(str$,rep$,with$) ln = len(rep$) ln1 = ln - 1 i = 1 while i <= len(str$) if mid$(str$,i,ln) = rep$ then strRep$ = strRep$ + with$ i = i + ln1 else strRep$ = strRep$ + mid$(str$,i,1) end if i = i + 1 WEND END FUNCTION
|
|
|
Sorting
Aug 31, 2019 9:57:39 GMT -5
Post by mpranger on Aug 31, 2019 9:57:39 GMT -5
Thanks for the responses.
I find tenochtitlanuk's examples to be the most helpful. I can study that and learn from it. However it's not exactly what I need for my program. My example file only had 4 entries for brevity. My actual files will have more and varying numbers of entries in which case the READ - DATA code doesn't work and trails off to things I don't understand how to adjust to read and sort my actual data file.
What I'm looking for is a way to read the file and sort the data on the fifth column. (three entries for brevity)
wid2.txt
" 3-",28,29,0,57 " 5-",57,59,0,116 " 6-",84,59,0,143
At this point I have just skipped the sorting. I am making the file with no problem and eyeballing it for the order instead of a descending sort on the 5th column. But it would be very cool if I could sort it. I can still copy it to the spreadsheet to sort but it's a lot of extra steps which I am trying to cut down on.
I know I have a lot to learn. But without a reference manual it's kind of confusing. I have had some luck doing a google search on "Liberty Basic and sort" for example. It pulls up examples. I just haven't found what I actually need for what I am doing.
I appreciate the help y'all offer. Once I have done something, I then know how to do it going forward.
Greg
|
|
|
Sorting
Aug 31, 2019 16:29:42 GMT -5
Post by tenochtitlanuk on Aug 31, 2019 16:29:42 GMT -5
Perhaps the following variation will help. I use inputcsv to get a row at a time into an array, stopping when the file ends. The code allows room for up to 100 rows max- you can increase that if you want.
" 1-",25,25,0,89 " 2-",29,29,0,58 " 3-",32,31,0,63 " 4-",37,64,0,101 " 3-",28,29,0,57 " 5-",57,59,0,116 " 6-",84,59,0,143 . . . saved as your input file and read in and analysed/sorted by
dim myArray4( 100, 5) ' you know it's 5 columns but not how many rows..
open "wid2.txt" for input as #fIn
for i =1 to 100 for j =1 to 5 inputcsv #fIn, myArray$( i, j) next j if eof( #fIn) then exit for next i
close #fIn
rowCount =i
print "Data as read into array"
for i =1 to rowCount for j =1 to 5 print myArray$( i, j), next j print "" next i
print "": print "Now sorting alphabetically on 5th column"
sort myArray$(), 1, rowCount, 5 ' rowCount 1 to rowCount sorted on column 5
for i =1 to rowCount for j =1 to 5 print myArray$( i, j), next j print "" next i
print "NOT what you wanted!"
print "" print "Let's replace the last column with leading zeroes to pad to the same length"
for row =1 to rowCount tmp$ =myArray$( row, 5) tmp$ =right$( "0000" +tmp$, 4) myArray$( row, 5) =tmp$ next row
print "Data as now held in the array"
for i =1 to rowCount for j =1 to 5 print myArray$( i, j), next j print "" next i
print "": print "Now sorting alphabetically on 5th column"
sort myArray$(), 1, rowCount, 5 ' rowCount 1 to rowCount sorted on column 5
for i =1 to rowCount for j =1 to 5 print myArray$( i, j), next j print "" next i
print "": print "What you wanted??? Whoops, wrong order!"
sort myArray$(), rowCount, 1, 5 ' rowCount 4 to 1 sorted on column 5
for i =1 to rowCount for j =1 to 5 print myArray$( i, j), next j print "" next i
print "Yeah!!"
end
The Help file is a pretty good manual, but keep asking here! We're glad to help others- we all clawed our way up the programming learning curve and have tied our brains in knots! It's also a good idea to step through the code watching as the variables change...
Best of luck...
|
|
|
Sorting
Sept 5, 2019 7:32:50 GMT -5
Post by mpranger on Sept 5, 2019 7:32:50 GMT -5
Thanks. I never would have figured that out. But I am studying it.
Greg
|
|