Merge rows where one column the has same content

[PJhacker]

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.

 

[Mike Lewis]

... 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

 

[Olaf Doschke]

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

INDEX ON Item Tag uItem UNIQUE

Which would give you
[pre] Item srp tpr tax date
12345 0.99 N
54321 77 42 N[/pre]

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

 

[GriffMG]

This should do it:

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 [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. ​

 

[Dan Olsson]

In line #2 don't you mean:

[b]COPY STRUCTURE TO RESULTS[/b]

or can you write it in that order also?

 

[GriffMG]

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


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. ​