×
INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!
  • Students Click Here

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

Jobs

Merge rows where one column the has same content

Merge rows where one column the has same content

Merge rows where one column the has same content

(OP)

I have a .dbf with thousands of records. I'm trying to get a unique list, based upon the Item column, with the value of the last non blank field from each column. In my example below I have item 12345 listed 3 time with different values in each column and item 54321 once. The result i'm looking for is a .dbf with with 2 records.

RE: Merge rows where one column the has same content

Quote:

... with the value of the last non blank field from each column ...

What to you mean by "the last"? The one with the latest date? The last record in physical order? Or what?

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips and downloads

RE: Merge rows where one column the has same content

Looks like a recipe to create invalid data.

The easiest way to get each first record with a key value is creating a unique index. In this case

CODE

INDEX ON Item Tag uItem UNIQUE 

Which would give you
 Item  srp  tpr  tax date
12345 0.99        N
54321   77  42    N 

And yes, I know that's not what you want, but a) the only SQL way to combine data based on common Item would be GROUP BY Item but there is no SQL aggregation function giving "first non-empty, non-null value", so that's not a standard way to group data and thus very questionable. And even when you could do that in some way it requires order and since dates are not always given the only possibility I see is the recno, like Mike also already assumed.

I'd say find a better way. If, for example, each record would have net price, tax, gross price, a missing tax value would rather be calculated from the prices than taken from a neighbor record, even though it would make sense if same Item means the same product with the same tax rate, you can get contradicting new tuples of data.

Since I don't know the meaning of the data I can't tell how that's working out in your case, but I'll pass this one, as that looks to me like a case you should rethink, it's nothing I ever saw in cleansing data and I did a lot of data migrations and data cleansing. I'd offer a tool to find such data gaps, list all rows of the same Item and offer manual data recombination. For example, it would be ideal to know how this happens and then being able to reconstruct original data that would result in such data gaps.

Bye, Olaf.

Olaf Doschke Software Engineering
https://www.doschke.name

RE: Merge rows where one column the has same content

This should do it:

CODE

SELECT MYTABLE
COPY TO RESULTS STRU
SELECT 0
USE RESULTS EXCLUSIVE
INDEX ON ITEM TAG RESULTS

SELECT MYTABLE

INDEX ON ITEM TAG MYTABLE
GO TOP
DO WHILE .NOT. EOF()
	m.ITEMNO = MYTABLE.ITEM
	DO WHILE .NOT. EOF() .AND. MYTABLE.ITEM = m.ITEMNO
		SELECT RESULTS
		IF !EMPTY(MYTABLE.SRP)
			SEEK (m.ITEMNO)
			IF !FOUND()
				APPEND BLANK
				REPLACE ITEM WITH m.ITEMNO
			ENDIF
			REPLACE SRP WITH MYTABLE.SRP
		ENDIF
		IF !EMPTY(MYTABLE.TPR)
			SEEK (m.ITEMNO)
			IF !FOUND()
				APPEND BLANK
				REPLACE ITEM WITH m.ITEMNO
			ENDIF
			REPLACE TPR WITH MYTABLE.TPR
		ENDIF
		IF !EMPTY(MYTABLE.TAX)
			SEEK (m.ITEMNO)
			IF !FOUND()
				APPEND BLANK
				REPLACE ITEM WITH m.ITEMNO
			ENDIF
			REPLACE TAX WITH MYTABLE.TAX
		ENDIF
		IF !EMPTY(MYTABLE.DATE)
			SEEK (m.ITEMNO)
			IF !FOUND()
				APPEND BLANK
				REPLACE ITEM WITH m.ITEMNO
			ENDIF
			REPLACE DATE WITH MYTABLE.DATE
		ENDIF
		SELECT MYTABLE
		SKIP
	ENDDO
ENDDO 

I have assumed that for items with no completed fields, you want no result

Regards

Griff
Keep Smileing

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.

RE: Merge rows where one column the has same content

In line #2 don't you mean:

CODE -->

COPY STRUCTURE TO RESULTS 
or can you write it in that order also?

RE: Merge rows where one column the has same content

Never thought about it, I think I use the old notation COPY TO NAME STRUCTURE [EXTENDED]…

Regards

Griff
Keep Smileing

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.

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members! Already a Member? Login

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close