Ken Kixmoeller/fh wrote:
> Hey - -- ---
>
> Anybody else working this AM?
>
> I am working on some data conversion, and I'm looking to see if
> anyone has a more clever technique than mine for resolving
> differences among duplicate records. What I mean is this: I have a
> lot of records where obviously-same people (for example) have
> different values populated. So I have 2, 3, 4, 5 records variously
> populated:
>
> person phone fax email (many
> more fields..)
> Sally Jones 222-333-4444 222-333-4445
> Sally Jones 222-333-4445
> Sally Jones 222-333-4444 sjo...@hercompany.org
>
> Of course, I want them all to end up:
> Sally Jones 222-333-4444 222-333-4445 sjo...@hercompany.org
>
>
> My technique essentially has always been:
> - scan the "like" ones
> - scatter the first one
> - apply a bunch of if !empty() statements to populate the variables
> - use SQL or "replaces" to repopulate them with the populated values.
>
> This is one of those situations where I keep thinking "there *has* to
> be a more clever way to do this." The problem is that I use the time-
> tested (or shop-worn) technique of flattening out the data, so there
> are a *lot* of fields that could be variously populated and some not
> to be evaluated like this, where the data in multiple rows are
> supposed to be different.
>
> Anybody do it differently?
>
> I am actually running the conversion in VFP9, so On-Topic!
>
> Ken
Don't know if I am understanding you, but considering you talk about "if
!empty()" statements (untested) :
Select person, max(phone) as 'phone', max(fax) as 'fax', max(email) as
'email'
into cursor mycursor
from mytable
group by person
order by person.
Then you replace the table with the cursor.
Or if you want to "fill" the original table (untested) :
update mytable
set mytable.phone = t1.phone
, mytable.fax = t1.fax
, mytable.email = t1.email
from (
Select person, max(phone) as 'phone', max(fax) as 'fax',
max(email) as 'email'
into cursor mycursor
from mytable
group by person
order by person.
) as t1
where t1.person = mytable.person
If there are more complex conditions than an !empty() then you might
want to use a "case..then..else..end" statement instead of "max()".
_______________________________________________
Post Messages to: ProFox@leafe.com
Subscription Maintenance: http://leafe.com/mailman/listinfo/profox
OT-free version of this list: http://leafe.com/mailman/listinfo/profoxtech
Searchable Archive: http://leafe.com/archives/search/profox
This message: http://leafe.com/archives/byMID/profox/49872c66.3040...@gmail.com
** All postings, unless explicitly stated otherwise, are the opinions of the
author, and do not constitute legal or medical advice. This statement is added
to the messages for those lawyers who are too stupid to see the obvious.
