# Filtering Columnar Data

Jim Ault JimAultWins at yahoo.com
Mon Jul 16 16:54:57 EDT 2007

```Yet another solution using SORT and complex sort criteria

---intro
1) the sort command says "sort using the result of the function"
2) the function returns a true/false
3) the result of the sort is that
the desired value in col 4 sorts to the top
4) the repeat loop scans each line to find
when the desired value is no longer in item 4
5) thus  complex/compound sort rules are possible
by placing them in the sortKeyTF() function

The magic is supplied by Rev since it will call the function for each line
of the container.  This function call provides the sort key to be used,
which is, in this case, is true false.  Any string or number can be returned
as the sort key.

--substantially based on Mark Schonewille's code

on toVariable
--we will build the lines of data to sort/filter
put   "100,101,102,99,104,105,106,107,108,109,110" into tStr
set cursor to watch
repeat with cnt = 1 to 1000
put cnt mod 10 into item 4 of tStr
put tStr & cr after tOutput
end repeat
replace comma with tab in tOutput --now tab delim
--     THIS is where we do the sort and extract
put 8 into myNumber
put the millisecs into tStart
put quickSortAndFilter(tOutput, myNumber) into onlyMyNumber
put the millisecs - tStart into elapsed
put elapsed && "millisecs" into msg
end toVariable

function quickSortAndFilter theData,myNumber
set the itemDel to tab
sort theData descending by (sortKeyTF(myNumber, item 4  of each))
--   the sort is done, now keep only the myNumber lines
repeat for each line myLine in theData
if item 4 of myLine is myNumber then
put myLine & cr after myNewData
else
exit repeat
end if
end repeat
return myNewData
end quickSortAndFilter

function sortKeyTF myNumber, thisItem4
return (1*myNumber) = (1*thisItem4)
end sortKeyTF

---- 41 millisecs to do 1000 lines, 171 msecs to do 10,000 lines
-- Mac G5 dual  1.8 mHz

Not the speediest way, but more powerful/flexible than just the filter
command, and probably on par with a RegEx solution.

Jim Ault
Las Vegas

On 7/16/07 1:14 PM, "Mark Schonewille" <m.schonewille at economy-x-talk.com>
wrote:

> Here's another solution. Really.
>
> function quickSortAndFilter theData,theItemNr,theFilter
>    set the itemDel to tab
>    sort lines of theData by (item theItemNr of each is not theFilter)
>    repeat for each line myLine in theData
>      if item theItemNr of myLine is theFilter then
>        put myLine & cr after myNewData
>      else
>        exit repeat
>      end if
>    end repeat
>    return myNewData
> end quickSortAndFilter
>
> Let me add some explanation:
> - theData is tab-delimited data
> - theItemNr is the number of the column you want to search
> - theFilter is the string you want to search for
>

```

More information about the Use-livecode mailing list