Grouping without sorting


#1

In Pano 6 I frequently use the Group function to create summary records at each value change in the column I’m grouping. In Pano X, I do not see a way group without sorting. Hopefully I’m missing something obvious.

John


#2

There is no way to group without sorting, and I’m not planning to add that. The way this code works in Panorama X it would require a complete rewrite to group without also sorting, and that feature was almost never used. You are the first person to ask about this in he two years since people have been using Panorama X.


#3

I continue to use it a lot on my older Mac w/ Pano 6 build 91236, great for
grouping time generated information combined with counting, and for some
stock market studies, plus some accounting I did for my puzzle business.


#4

Having the ability to procedurally create Summary records means that a user could create a customized GroupUp for an ‘Unsorted Field’

Something like this

Local LCurrentRecordValue
FirstRecord
LCurrentRecordValue = «Unsorted Field»
DownRecord
Loop
If «Unsorted Field» ≠ LCurrentRecordValue
    UpRecord
    InsertBelow
    SummaryLevel 1
    DownRecord
    LCurrentRecordValue = «Unsorted Field»
EndIf
DownRecord
Until Info("Stopped") = 'True'
DownRecord
InsertBelow
SummaryLevel 1
Field «Unsorted Field» Propagate
FirstRecord

The code above wasn’t liking the Until designating the end of the loop so the last Summary record will not be created and the Propagate won’t happen but I’m too burnt at the end of my day to figure out why. :wink: That will be yours to figure out. My Panorama X was beachballing on my code. Probably something about attempting to add that last Summary Record with the InsertBelow command.


#5

I was kind of hoping someone would figure that out and post it :slight_smile:

However, it would be faster if the loop used a variable and findbelow. Then it wouldn’t have to loop every record. I think you could just replace downrecord with

findbelow «Unsorted Field»

and change the until to use info("found") = false()

Also, you could change «Unsorted Field» to just «» and it would work for the current field.

BTW, the problem with your until statement was that you used 'true', you should have used true().


#6

Ok, here is a working version that uses the findbelow statement. It’s pretty fast if there aren’t too many summary records needed. It also moves the current line back to wherever it started.

startdatabasechange "allrecords"
local value, rid
noshow
firstrecord
value = «»
rid = info("serverrecordid")
loop
    findbelow «» <> value
    stoploopif info("found") = false()
    uprecord
    insertbelow
    summarylevel 1
    downrecord
    value = «»
endloop
addrecord
summarylevel 1
propagate
addrecord
summarylevel 2
find rid = info("serverrecordid")
endnoshow
showpage

Perhaps I’ll make this a statement, but I probably won’t add it to the menus.


#7

Yep, much better.

I take offense pretty quick when I see someone post that ‘There is no way to…’ when they are speaking of Panorama. Most always, there is a way.


#8

I tested your routine on a file with over 5000 records… took a while but worked nicely! Thank you much.


#9

I posted a similar thread about grouping without sorting and Jim told me the same thing…no one does that. Well, I do and liked using it in Pan 6. Wouldn’t a simple way of keeping a fixed order is to create a number field and sequence the original order and then use that field to do the final group up at the end? I would think that process would run extremely fast, no matter how many records there were.


#10

I do use sequence fields in some cases when I want to restore an order.
Frequently though when grouping without sorting, the file is a throw-away
file on which I’m doing temporary work and sequencing is irrelevant.


#11

In Pan 6, I used to use, “Group without sorting”, all the time, but I never told anyone!