auto code filling 4

[Phil Thoms]

[tt][pre][tt][tt]Hello,
I've received a database with about 5000 records. There's an ID field together with a Group code field as follows:-

ID-------Group
987691
987691 G99
987691 G99
654230
654230 G233
888765
888765 H91

etc., etc. As you can see the first occurrence of the group field for each ID is missing. Same ID/Group may take up to 2 records or as many as 7 records.
Is there a quick way of rectifying this problem of filling in the blanks?

As always, your help is much appreciated.

 

[Mike Lewis]

No doubt there are many ways of doing this. The following is just off the top of my head - not tested:

SELECT ID, MAX(Group) FROM TheTable GROUP BY ID INTO CURSOR csrTemp
SELECT csrTemp
SCAN
  UPDATE TheTable SET Group = csrTemp.Group WHERE ID = csrTemp.ID
ENDSCAN

Give it a try.

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips and downloads

 

[Mike Lewis]

Actually, I'm not sure that Group is a sensible name for a field. It might get confused with the GROUP BY clause.

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips and downloads

 

[Mike Lewis]

Sorry - small mistake. Change the first line as follows:

SELECT ID, MAX(Group) [b]AS Group[/b] FROM TheTable GROUP BY ID INTO CURSOR csrTemp

But keeping in mind what I said about using Group as a field name.

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips and downloads

 

[Mike Lewis]

A slightly simpler version of the above:

SELECT ID, MAX(Group) AS Group FROM TheTable GROUP BY ID INTO CURSOR csrTemp
UPDATE TheTable SET TheTable.Group = csrTemp.Group FROM csrTemp WHERE TheTable.id = csrTemp.id

I'll shut up now.

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips and downloads

 

[GriffMG]

Does the group ever have only one record (i.e. one record with no group code)

Regards

Griff
Keep [Smile]ing

There are 10 kinds of people in the world, those who understand binary and those who don't.

I'm trying to cut down on the use of shrieks (exclamation marks), I'm told they are !good for you. ​

 

[Phil Thoms]

Mike,
Code not working. Error= 'command contains unrecognized phrase/keyword'
As suggested I've changed the fieldname group to gcode

GriffMG,
Answer to your question is no. 2 records minimum.

Thanks

 

[GriffMG]

I would do this:

use MyTable exclusive
index on id+group tag myorder descending
go top
do while .not. eof()
  m.MyKey = MyTable.ID
  m.MyGroup = MyTable.Group
  skip
  do while .not. eof() .and. MyTable.ID = m.MyKey
    if empty(group)
      replace group with m.MyGroup
    endif
    skip
  enddo
enddo

Regards

Griff
Keep [Smile]ing

There are 10 kinds of people in the world, those who understand binary and those who don't.

I'm trying to cut down on the use of shrieks (exclamation marks), I'm told they are !good for you. ​

 

[Phil Thoms]

GriffMG,
Your code works OK, thanks.

Mike,
It's the second line of your code that gives the message 'command contains unrecognized phrase/keyword'

Thanks.

 

[GriffMG]

No problem, not as elegant as two lines of SQL, but given the circumstances it should be quick

Regards

Griff
Keep [Smile]ing

There are 10 kinds of people in the world, those who understand binary and those who don't.

I'm trying to cut down on the use of shrieks (exclamation marks), I'm told they are !good for you. ​

 

[Mike Lewis]

It's the second line of your code that gives the message 'command contains unrecognized phrase/keyword'

Well, I've just created a table like yours, and run my code. It works OK, without an error. Are you sure you copied it exactly, and correctly substituted your table name?

I appreciate that Griff has given you code that works, but it would be nice to know how the error in my code arose. Did you try my earlier suggestion - the one with the SCAN loop?

Mike






__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips and downloads

 

[Phil Thoms]

Mike,
I've probably misspelled something. I changed the fieldname 'group' (which didn't help) to gcode. I'll retry both suggestions and let you know.
I do like the brevity of SQL.

Many thanks.

 

[Mike Lewis]

Thanks fot that, Philtoms. I realise that you already have a working solution, but it would be good to get to the bottom of the error that you saw, if only for the benefit of anyone else who might want to try the SQL code.

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips and downloads

 

[Olaf Doschke]

You are using VFP6, right? I don't see a specialty in Mikes SQL not working in that version, too, but it might be version related.

I'd have another question about the correspondence of ID and group field, is there any group value attached to different IDs, or simpler asked, would grouping by ID create the same groups as grouping by group?

As a counter example you might have:

[pre] 987691
987691 G99
987691 G99
654230
654230 G233
888765
888765 H91

...

987691
987691 Z23
987691 Z23[/pre]

If that would be the case Mikes code would find Z23 and also replace the upper three 987691 records with Z23. Running via a scan..endscan can be better in that case, especially when just scanning the data in reverse recno order, when you store group in a variable just before endscan and find a gap you can fill in that last known group code there, provided the ID matches.

use MyTable exclusive
index on recno() tag reverse order descending
go top
m.MyID = 0
m.MyGroup = ""
scan
   If empty(group) and !empty(m.myID) and m.MyID=ID
      replace group with m.MyGroup
   Endif
   m.MyID = ID
   m.MyGroup = Group
endscan

This only acts local on each group, but it only works on the assumption the data as you show it is in physical order and so reverse order is helpful to find the group value in the "first" record (because in reverse order each last group record comes first).

Bye, Olaf.

 

[Phil Thoms]

Mike,
Your version with Scan/Endscan works fine. I did detect a misspelling of mine in the other version.

Olaf,
Yes I'm still using VFP6 until I have time to get a later version VFP7 or VFP9 as per your recent recommendations. Are updates available or do you need to buy full version?
The database in question is ordered by ID. I see the code you have sent is similar to GriffMG's.

Many thanks

 

[Mike Lewis]

You are using VFP6, right? I don't see a specialty in Mikes SQL not working in that version, too, but it might be version related.

If Philthoms is on VFP 6, then that would explain the error. In 6, the UPDATE command does not support the FROM clause. However, my earlier code, where the UPDATE is contained in a SCAN loop, should work fine in 6.

Mike


__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips and downloads

 

[Olaf Doschke]

You don't get the newer enhanced UPDATE-SQL statement as service pack, it's not a bug of VFP6, but simply a lack of the capability of that older SQL engine. This is the kind of progress developers want paid, it's not just a bugfix, it's the enhancement of possibilities, so such enhancements of VFP7,8,9 cost something, of course. SQL is not something ever complete like the four base arithemtic operations and even having a calculator showing +,-,x and ÷ buttons you still have quality differences in the range of values and precison it can operate on, performance, other operations,.... You may think of SQL as even only having three base operations INSERT, UPDATE, and DELETE, but there's much more to it and complexity grows with the possibilities of subqueries and other details.

As owner of VFP6 you can get the cheaper update price not only for VFP7, but also of VFP9, if you could still buy VFP9. You may find some remaining stock or used versions somewhere. From MS you may only get it through some subscriptions.

My code is similar to Griffs, yes, but it doesn't sort by ID descending, it sorts reverse of the physical order of the file, which from your sample data would not be sorted by ID.

If you have any record after querying [tt]SELECT ID, COUNT(DISTINCT group) FROM yourTable GROUP BY ID WHERE !EMPTY(group) HAVING COUNT(group)>1[/tt], then both Mikes and Griffs code will write a wrong group codes to one of the groups having that ID. I assume that is the case, as group with just three characters has less possible values than the number of IDs you have there. You'll fill up gaps wrongly. Mikes and Griffs code assume any group code only occurs with the same ID, I don't think so, as there are much more IDs (range just below a million) than possible group codes (2600). As your table only has 5000 records, that might not be the case, but it's worth to test, if you don't mingle two groups.

As an example:

CREATE CURSOR yourTable (id i, group c(4))
INSERT INTO yourTable Values(987691,'')
INSERT INTO yourTable Values(987691,'G99')
INSERT INTO yourTable Values(987691,'G99')
INSERT INTO yourTable Values(654230,'') 
INSERT INTO yourTable Values(654230,'G233')
INSERT INTO yourTable Values(888765,'') 
INSERT INTO yourTable Values(888765,'H91')
*...
INSERT INTO yourTable Values(987691,'') 
INSERT INTO yourTable Values(987691,'Z23')
INSERT INTO yourTable Values(987691,'Z23') 

SELECT ID, COUNT(DISTINCT group) FROM yourTable GROUP BY ID WHERE !EMPTY(group) HAVING COUNT(DISTINCT group)>1

It might again fail on VFP6s missing sql capabilities needed here, ie the COUNT(DISTINCT field).

Bye, Olaf.

 

[atlopes]

Actually, a single line of SQL code would be sufficient, in VFP9.

CREATE CURSOR curTable (idNum I, codeChar C(4))

INSERT INTO curTable VALUES (987691, '')
INSERT INTO curTable VALUES (987691, 'G99')
INSERT INTO curTable VALUES (987691, 'G99')
INSERT INTO curTable VALUES (654230, '')
INSERT INTO curTable VALUES (654230, 'G233')
INSERT INTO curTable VALUES (888765, '')
INSERT INTO curTable VALUES (888765, 'H91')

UPDATE curTable SET codeChar = ct.codeChar FROM curTable ct WHERE ct.idNum = curTable.idNum AND EMPTY(curTable.codeChar) AND !EMPTY(ct.codechar)

BROWSE

VFP9 is available through Microsoft subscription plans (MSDN and others), from which you also gain access to much more applications and development platforms.

 

[Olaf Doschke]

CLOSE TABLES All

CREATE CURSOR yourTable (id i, agroup c(4))
INSERT INTO yourTable Values(987691,'')
INSERT INTO yourTable Values(987691,'G99')
INSERT INTO yourTable Values(987691,'G99')
INSERT INTO yourTable Values(654230,'') 
INSERT INTO yourTable Values(654230,'G233')
INSERT INTO yourTable Values(888765,'') 
INSERT INTO yourTable Values(888765,'H91')
INSERT INTO yourTable Values(987691,'') 
INSERT INTO yourTable Values(987691,'Z23')
INSERT INTO yourTable Values(987691,'Z23') 


SELECT * FROM yourTable INTO CURSOR MikesTable ReadWrite
SELECT * FROM yourTable INTO CURSOR GriffsTable ReadWrite
SELECT * FROM yourTable INTO CURSOR AtlopesTable ReadWrite
SELECT * FROM yourTable INTO CURSOR MyTable ReadWrite

*-- Mike
SELECT ID, MAX(aGroup) AS aGroup FROM MikesTable GROUP BY ID INTO CURSOR csrTemp 
UPDATE MikesTable SET MikesTable.aGroup = csrTemp.aGroup FROM csrTemp WHERE MikesTable.id = csrTemp.id 

*-- Griff
SELECT GriffsTable 
index on STR(id)+agroup tag myorder descending
go top
do while .not. eof()
  m.MyKey = GriffsTable.ID
  m.MyGroup = GriffsTable.aGroup
  skip
  do while .not. eof() .and. GriffsTable.ID = m.MyKey
    if empty(agroup)
      replace agroup with m.MyGroup
    endif
    skip
  enddo
enddo 

*-- Atlopes
UPDATE AtlopesTable SET agroup = ct.agroup ;
FROM AtlopesTable ct ;
WHERE ct.id = AtlopesTable.id AND EMPTY(AtlopesTable.agroup) AND !EMPTY(ct.agroup)

*-- Me
SELECT MyTable
index on recno() tag reverse descending
go top
m.MyID = 0
m.MyGroup = ""
scan
   If empty(agroup) and !empty(m.myID) and m.MyID=ID
      replace agroup with m.MyGroup
   Endif
   m.MyID = ID
   m.MyGroup = aGroup
endscan

See? The errors marked may not happen in data not having this situation of two groups with same ID and different group codes, so I'm not blaming anyone to have given wrong code, but everybody made the assumption such situation doesn't exist in your data. You can't say that from looking at some sample rows only. The probabilities suggest it can be the case.

Though Mikes code has the largest marked sections, it does put all rows with same ID to the same group code, in that aspect it's fully correct again, but only my code would respect something like a repeated ID as local group and only take a found code to the closest gap and not further up or down. Finally, my code also makes an assumption not necessarily true, in having the groups in recno order (no matter if reverse or not), if that's not the case the code wouldn't fill most gaps. Final truth is make sure IDs are a single group code all (aside of the empty codes) and then you can use any mechanism to fill the gaps via the non empty value for each ID.

Bye, Olaf.

Notice: Atlopes query really needed the group field to be named different, otherwise the query has a syntax error, no big problem, though. And my code was fixed in the INDEX line.

 

[Phil Thoms]

Olaf,
Thanks for your thoroughness and continuous help. Based on your latest info the coding in my database seems fine.
Once again thank you to all.