Consolidate multiple fields per record to multiple records with 1 field 3

[ladyhawkfl]

I have a table with 1280 records and 208 fields. I need to keep the first 3 fields, (field names: a, b, a0001) for all records. I then need to take all the records where the next field a0002 is populated and replace field a0001 with field a0002 and append fields a, b, a0001 to the file. I know this will mean duplicate records for the first two fields, but that's what I need. Ultimately, I need one new record for every instance where fields a0002 through a0206 are populated with the first 2 fields retained and the value of field a0001 overwritten. I've attached a sample of 25 records. Thank you!

 

[GriffMG]

Try this:

CLOSE ALL
CLEAR
USE D:\$INCOMING\TAILSDATA\SAMPLE
SELECT 0
CREATE TABLE D:\$INCOMING\TAILSDATA\RESULTS (A C(40) NOT NULL, B C(18) NOT NULL, A0001 C(5) NOT NULL)
SELECT SAMPLE
GO TOP
DO WHILE .NOT. EOF()
	SELECT RESULTS
	APPEND BLANK
	REPLACE A WITH SAMPLE.A
	REPLACE B WITH SAMPLE.B
	REPLACE A0001 WITH SAMPLE.A0001
	? A,B

	FOR I = 2 TO 206
		MFIELDNAME = "sample.a"+RIGHT("000"+ALLTRIM(STR(I,4,0)),4)
		MFIELDVALUE = EVALUATE(MFIELDNAME)
		?? MFIELDVALUE
		IF !EMPTY(MFIELDVALUE)
			SELECT RESULTS
			APPEND BLANK
			REPLACE A WITH SAMPLE.A
			REPLACE B WITH SAMPLE.B
			REPLACE A0001 WITH MFIELDVALUE
		ELSE
			I = 207
		ENDIF
	NEXT
	SELECT SAMPLE
	SKIP
ENDDO
SELECT RESULTS
GO TOP
BROWSE

I made two assumptions:

1) All A0001 have some kind of value
2) If an Annnn is blank the rest of the record need not be processed

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

 

[Olaf Doschke]

While this could be done differently in detail, Griff's solution is in the direction of what's applicable. You could also do a SQL union of selects of a,b,a0001 with a,b,a0002 as a0001 and a,b,a0003 as a0001 and so on, it's easier to go for a loop.

If Griffs assumption about the rest of the fields is wrong, simply don't set i=207 and continue to go through the rest to find further non-empty values. You could also check for ISNULL() instead of EMPTY() or additional to EMPTY(). And I'd go for FIELD(N) instead of composing a filename from "a" plus counter turned into a 4 digit number string with leading zeros. the loop would just need to go from whatever field number a0001 is to the last field.

Bye, Olaf.

Olaf Doschke Software Engineering

https://www.doschke.name

 

[GriffMG]

I think Olaf is suggesting it could be optimised a little to something like this:

CLOSE ALL
CLEAR
USE D:\$INCOMING\TAILSDATA\SAMPLE
SELECT 0
CREATE TABLE D:\$INCOMING\TAILSDATA\RESULTS (A C(40) NOT NULL, B C(18) NOT NULL, A0001 C(5) NOT NULL)
SELECT SAMPLE
GO TOP
SCAN
	SELECT RESULTS
	APPEND BLANK
	REPLACE A WITH SAMPLE.A, B WITH SAMPLE.B, A0001 WITH SAMPLE.A0001
	? A,B
	FOR I = 4 TO 208
		SELECT SAMPLE
		MFIELDVALUE = EVALUATE(FIELD[I])
		?? MFIELDVALUE+" "
		IF !EMPTY(MFIELDVALUE)
			SELECT RESULTS
			APPEND BLANK
			REPLACE A WITH SAMPLE.A, B WITH SAMPLE.B, A0001 WITH MFIELDVALUE
		ENDIF
	NEXT
ENDSCAN	
SELECT RESULTS
GO TOP
BROWSE

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

 

[Olaf Doschke]

I didn't even address DO WHILE vs SCAN, but you know the usual comments on this.

In detail, I never learned when SCAN was introduced and you could change from DO WHILE loops, surely INSERT-SQL was later and may not be available when legacy Foxpro is used here.

Bye, Olaf.

Olaf Doschke Software Engineering

https://www.doschke.name

 

[GriffMG]

The syntax for the SQL select version could look like this:

SELECT  a,b,a0001 FROM sample UNION ALL (Select a,b, a0002 as A0001 FROM sample WHERE !EMPTY(a0002)) UNION ALL (Select a,b, a0003 as A0001 FROM sample WHERE !EMPTY(a0003)) INTO CURSOR RESULTS

That is going to become quite a long command with 206 fields, but could be done in chunks, combining say ten at a time, then combining the all results - that would be readable and manageable - thanks for the idea Olaf, I don't 'think SQL' like you do obviously!

And I do not instinctively use SCAN, no good reason, just habit.

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

 

[GriffMG]

Hmm... I am clearly at a bit of a loose end here

May not be technically the best solution, but...

CLOSE ALL
CLEAR
USE D:\$INCOMING\TAILSDATA\SAMPLE
SELECT 0
CREATE TABLE D:\$INCOMING\TAILSDATA\RESULTS (A C(40) NOT NULL, B C(18) NOT NULL, A0001 C(5) NOT NULL)
SELECT SAMPLE
GO TOP
SCAN
	SELECT RESULTS
	APPEND BLANK
	REPLACE A WITH SAMPLE.A, B WITH SAMPLE.B, A0001 WITH SAMPLE.A0001
	? A,B
	FOR I = 4 TO 208
		SELECT RESULTS
		APPEND BLANK
		REPLACE A WITH SAMPLE.A, B WITH SAMPLE.B, A0001 WITH EVALUATE("SAMPLE."+FIELD(I,"SAMPLE"))
	NEXT
ENDSCAN	
SELECT RESULTS
DELETE FOR EMPTY(A0001)
PACK
GO TOP
BROWSE

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

 

[Olaf Doschke]

Well, Griff, for the reson of that many fields the SQL solution is not applicable. VFP9 raised the number of unions to more than 10, somewhat no limit, but you still have a complexity and command length limit. No, a non-SQL solution is really fine here, especially when it should be runnable in legacy Foxpro itself. I just mentioned it to show how unwieldy long the SQL solution becomes in this case. You could split it up, etc. but then the SCANloop is fine enough.

Bye, Olaf.

Olaf Doschke Software Engineering

https://www.doschke.name

 

[ladyhawkfl]

Griff, I used your first solution and it worked perfectly. thank you so very much!!!

 

[GriffMG]

Thanks for giving me something to challenge me more than a sudoku

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

 

[Ostyno]

Hello, please, i found out that the drive that is attached on Micros Server which is not meant to be removed was removed by one. Since then, my workstations cannot access the Micros/Opera server. After inserting it back yet its not connecting. Please, what should i do?

 

[Mike Lewis]

Please, what should i do?

There are two things you can do:

First, find a forum that is relevant to your question, and post it there.

Secondly, refrain from tagging your problem onto other people's threads.

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips and downloads