Sorting issues 4

[linousa]

I have table, that I am sorting to by several keys(zip+recnum+..), but when I using commands like "replace next 20...", it applies those changes to the records from initial order before sort.
So I came up with workaround- saving those tables to txt files after sorting and reopening them again from those files, but it drastically slows processing time.
Does anyone know any better way of doing it?

 

[Olaf Doschke]

The scope next 20 does act on records in current index order. So did you create an index or just sort with SQL ORDER BY clause? What's your code?

Bye, Olaf.

 

[linousa]

Creating index manually by table properties, not by command

 

[Olaf Doschke]

And then? If this index is not used, then you replace in the normal order. And how did you define the index manually? What expression do you use?
If zip is numeric and recnum just means RECNO you get weird sort order, as 12345+1 is not 123451 but 12346, take a look at the order you really get from your index.

Bye, Olaf.

 

[linousa]

This is one of many:
Creating index through: data session -> table properties -> indexes tab ->order name(zipacc), expression(zip+recnum+accnum+subacc).
Call it by: set order to zipacc

 

[dbMark]

Are you creating the index but not activating it? There are several ways to activate a specific index order. One way is to include the clause in the USE command. Or after you've opened the table you can select which index to become active. (All indexes will be updated in the background but only one can be active for display/processing use.)
SET ORDER TO {ordername)

This will deactivate all indexes but they will still update in the background:
SET ORDER TO

Many years ago in early versions (dBase, FoxBase) the BROWSE screen used to have a BROWSE NOFOLLOW command so that if you modified a displayed indexed field then the record would move to the new position but you would remain where you were in the displayed list. It's gone now and I don't know of any alternate way to keep your place in a list if you modify an active indexed field.

 

[linousa]

yes, I activate it by, from my previous post, by:

SET ORDER TO ZIP

command, index set to zip(zip+recnum), and if I execute:

GOTO 100
REPLACE NEXT 5 zip WITH '00000'

it will replace 100,1,2,3 and 4th records, instead of 100-104th. Thus, it applies changes like:

REPLACE zip WITH '00000'
(sort,moves the cursor to the top)
SKIP
2..
3..
4..
REPLACE zip WITH '00000'
(sort)
SKIP

But I need to replace 100-104, my guess is to make another column and merge them at the end. Is it possible to insert column while running?

 

[Dan Freeman]

DANGEROUS WATER HERE

REPLACE NEXT <n> with an order set alters the position of the first record replaced, so it alters the set of records processed with NEXT <n>.

I seriously question the logic of REPLACE NEXT <N>. What is the condition that determines <N>? Can't you just REPLACE FOR <condition>?

 

[linousa]

Basically, here I need to go through the list and find the records with the same member numbers and there more than five records of the same member number I need to sort them to top and keep them in the order, that's where inserting a new field on the fly will be handy, I just not sure if it's can be done.

 

[JRB-Bldr]

inserting a new field on the fly will be handy

Well, I am not sure if it would be the best way to do what you want to do but...

If the table is in USE EXCLUSIVE then you can use the ALTER TABLE command to ADD fields (columns).
In your VFP Command window type: HELP ALTER TABLE - SQL and study up on the various things you can do.
For Example: ALTER TABLE Customer ADD COLUMN Fax C(20) NULL

However you might want to spend some more time looking at what has been mentioned above about how you are approaching the problem. There might be a better way that is very different than what you are doing.

Good Luck,
JRB-Bldr

 

[mmerlinn]

Are you doing what I think you are doing? Setting the order to zip, then modifying the contents of the index? Therefore changing the position of the next record in the index?

It looks like you are changing the index of record 100 to '00000' which will AUTOMATICALLY change your index and place that record at position #1 in the index meaning that the NEXT record after record 100 is what used to be record 1, but is now record 2.

When you need to change an indexed column, it is better to pull the records you want to change into a cursor, change them, then write them back to the table. I am sure there are other safe ways to do what you want, but changing it the way you are trying to do will give you garbled information.

mmerlinn

http://mmerlinn.com

Poor people do not hire employees. If you soak the rich, who are you going to work for?

"We've found by experience that people who are careless and sloppy writers are usually also careless and sloppy at thinking and coding. Answering questions for careless and sloppy thinkers is not rewarding." - Eric Raymond

 

[Olaf Doschke]

I think you're fooling yourself, since you change zip you change the order of first record you replace to be the 1st record, and then of course continue with the lowest zip >00000.

You mainly sort by zip with your index, while you say you want to process all records of a certain member, a certain accnum I assume, then why such an index order?

If you mainly want to replace all rows of accnum, then your index expression must start with accnum and the simplest way to get all rows even without first counting them is REPLACE ... IN yourtable FOR accnum = X.

Bye, Olaf.

 

[dbMark]

Do the fields you're changing contain identical field data that isn't duplicated in the other records? If so, then once you determine what that data is then you can exclude the non-matching records and proceed with replacements.

cThisValue = ZipField
SET FILTER TO ZipField = cThisValue
REPLACE ALL ZipField WITH '00000'
SET FILTER TO

If the values are different or duplicated then you would have to determine the best way to identify the multiple records.

Admittedly, this is a simple solution, there are surely better (newer) approaches that became available as the VFP language matured.

 

[Dan Freeman]

Is this an actual US zip code? Why are you changing it? It is assigned by the US Postal Service. Changing is fishy from the outset.

Basically, here I need to go through the list and find the records with the same member numbers and there more than five records of the same member number I need to sort them to top and keep them in the order, that's where inserting a new field on the fly will be handy, I just not sure if it's can be done.

If all you want to do is find the customers who have five or more records, try something like this:

Select MemberNumber, Count(*) As Howmany ;
  From YourTable ;
 Group By 1 ;
Having Howmany >= 5 ;
  Into Cursor Howmany

Then you have a list you can work from. Altering a data element that isn't "yours" is almost never the right way to go.

 

[linousa]

Problem solved, I have inserted sorting code field, but I want to check if you have a better idea solving this: we have a list, where 1 record is 1 account, first step is to sort 6 or more accounts with the same address(also called over-sized) to the top of our list and export total count of records and number of groups created, next step is to group records with the same substr(zip,1,5) and export counts for each group, excluding over-sized ones and 3rd is to group rest with substr(zip,1,3) exporting counts and groups too.

 

[Olaf Doschke]

I suggest you start a new question or afew new questions and explain in a littlemore detail.

You puzzle me with severaldetails:

1. "first step is to sort 6 or more accounts with the same address(also called over-sized) to the top of our list and export total count of records and number of groups created"
If you sort by any indey you always sort the whole table, so what are you saying here? a) what if there are less than 6? What is with the rest of the list?
If you sort by zip you sort all accounts by zip.

(If not yet comprehended, replacing zip while an index on zip+x is in effect you change processing order, indeed a separate sort field is a solution)

By sorting you don't group, you have SQL GROUP BY for grouping, which means to create 1 record per group. If you sort you only sort. Grouping is not done in the SQL meaning of the word, but of course all records with same zip come in sort order, so you can see groups of more or less records with same zip. It's not needed for grouping, you can also GROUP B without sorting.

2. next step is to group records with the same substr(zip,1,5) and export counts for each group, excluding over-sized ones
As said, you don't need to sort first.

3. group rest with substr(zip,1,3) exporting counts and groups too
There is no rest, is there?

Solutions:
1: [tt]Select Count(*) as Count, LEFT(zip,5) as zip5 From yourtable GROUP BY 2 HAVING Count(*)>=6 Into Cursor crsOversized[/tt]
2: [tt]Select Count(*) as Count, LEFT(zip,5) as zip5 From yourtable GROUP BY 2 HAVING Count(*)<6 Into Cursor crsNormal[/tt]
_Tally is our group count in both cases, or RecCount().

It will help to have an index on Left(zip,5), no index on anything else does accelerate this and sorting is not needed. That would only help you to manually do what the group by clause does.

There is nothing left over for the left 3 digits, all data is grouped by left 5 zip digits and counted either in crsOversized or crsNormal. If you now additionally want to group by LEFT(zip,3) you can do it the same way, but this will just create fewer each larger groups. Records you already grouped by LEFT(zip,5) are all in there, too, there is no "rest", the zip3 groups will aggregate all 5digit zip groups starting with the same 3 first digits.

Bye, Olaf.

 

[Dan Freeman]

I'll pick a nit here, Olaf, and point out that Count is a HORRIBLE column name because it is BOTH the name of a Foxpro command and a function used in both SQL and the Calculate command. (That's why I used HowMany as my column name in the example I posted earlier.)

 

[Olaf Doschke]

That's okay, in regard of naming conventions recommended in the help you could use field names iCount or nCount and cZIP5.

I should use that very strictly, as I'm also so strict about this so often, but it's quite lost as a small island of compliance in a database using field names without type prefix. Using reserved words can be a more severe issue, especially with field names. If you could ever name a field _tally the side effect of addressing it would be interesting, but actually VFP forbids such field names, though in general names starting with an underscore are allowed.

It also doesn't pay to retrofit naming conventions into code not applying it, there are normally other maintainability issues more severe, typically such applications work and so have proven in time the rule violations are not having side effects. You could blame VFP to allow using reserved words for variable names etc.

Bye, Olaf.

 

[Dan Freeman]

Yeah, I did say it was picking a nit. We're talking about a language where something like this is perfectly legal:

Count to Count

Legal doesn't equal a good idea.

I deal all day long with code that only sometimes sticks to a naming convention, among several of them in the soup. And then I'll bump into a process-control variable named CMONTH that stores an integer. <sigh>

I once worked for a grumpy old man that drilled into me the idea that you should code as if the guy taking over after you is a homicidal maniac who knows where you live.

 

[Olaf Doschke]

Something I also heard often already. You make me think, I know where some previous developers live...

Bye, Olaf.