Join multiple records (same field) into one record

[Roderick]

I have a table of students and classes that looks like this:

John Doe, Math101
John Doe, English101
John Doe, Science102
Lisa Smith, Math101
Jane Johnston, Math101
Jane Johnston, English101

I need to create a data file that looks like this:

John Doe, Math101,English101,Science102
Lisa Smith, Math101
Jane Johnston, Math101,English101

I've looked into Joins on the message boards but most merge multiple different fields. I am trying to merge multiples of the same field to the corresponding student. The number of classes for one student may vary.

Can someone point me in the right direction?

Thanks,

Roderick

[Tom Cone Jr]

Re: Join multiple records (same field) into one record

Is the desired output (data) file a simple text file? ... a field in a table?

First thought here would be a simple script to step through the records and use a nested loop to capture the classnames for each student. Would an example of that be of use to you?

[Roderick]

Re: Join multiple records (same field) into one record

Yes it is for a CSV file. Although converting it to a table would not be bad either.

An example script would be awesome to have as a starting point.

[Tom Cone Jr]

Re: Join multiple records (same field) into one record

Here you go. Let me know if you have questions.

-- tom

[Stan Mathews]

Re: Join multiple records (same field) into one record

And another approach.

Run the stud script and examine the file created in the c:\ root directory.

[Roderick]

Re: Join multiple records (same field) into one record

WOW. Thanks guys.

I am going to mess around with it tonight and post my final script.

[Roderick]

Re: Join multiple records (same field) into one record

Stan,

I edited your script to fit my table (includes studentID, firstname, lastname, class, class, etc). I guess it would have been easier had I included this information on the original post but I am learning by editing.

The real table is:

123456789,John,Doe,Math1
123456789,John,Doe,English1
987654321,Lisa,Smith,Math1
etc.

Everything looked good but I noticed that the first student had 11 classes and then the second student only had 11 classes (missing two). The thrid student started with the correct class. Can you check my code?

tbl = table.open("Students")
tbl.query_create("N",".T.","student")
tbl.fetch_first()
fil = file.create("C:\upload.txt",FILE_RW_EXCLUSIVE)
WHILE .not. tbl.fetch_eof()
	osis = alltrim(tbl.Student)
	firstname = alltrim(tbl.Firstname)
	lastname = alltrim(tbl.Lastname)
	class = strtran(table.external_record_content_get("Students","alltrim(class)","","student = "+quote(osis)),crlf(),",")
	fil.write_line(osis+","+firstname+","+lastname+","+class)
	WHILE tbl.student = osis .and. .not. tbl.fetch_eof()
		tbl.fetch_next()
	END WHILE
END WHILE
fil.flush()
fil.close()
tbl.close()

[Roderick]

Re: Join multiple records (same field) into one record

Tom,

I edited your script but the student's names are all mixed up. Ha ha.

My table is actually:
StudentID,firstname,lastname,class,class,class,etc.

Also, how would I write this to a file? I tried to use Stan's method within your script but that did not work.

Here is the code:

t = table.open("students")
t.index_primary_Put("student")	'sort table by student
t.fetch_first()	'start at top of table

dim stu_list as C = "" 			'this is the target for output; a crlf delimited list
dim curr_osis as C = ""			'this will hold the current student's id
dim curr_firstname as C = ""     'this will hold the current student firstname
dim curr_lastname as C = ""     'this will hold the current student lastname
dim curr_stu_classes as C  = "" 'this will hold the comma & quote delimited string for single student

while .not. t.fetch_eof()   'loop through all the records
	curr_stu_classes = ""	'empty the current string at top of loop
	curr_osis = trim(t.student)
	curr_firstname = trim(t.Firstname)
	curr_lastname = trim(t.Lastname) 
	curr_stu_classes = curr_osis + "," + curr_firstname + "," + curr_lastname 'begin the current string with name
	while trim(t.Student) = curr_osis .and. .not. t.fetch_eof()
		'loop through this student's classes
		curr_stu_classes = curr_stu_classes + "," + trim(t.class)
		t.fetch_next()  	'get next record; 
							'at this point we may be on same stu's next class OR
							'we may be on next student; inner loop ends if on
							'next student
	end while 	'rec pointer on next student or past eof
	stu_list = stu_list + curr_stu_classes + crlf()  'add string to target list
end while 		'rec pointer past eof							

t.close()  	'close source table

trace.writeln(trim(stu_list))  'write list to trace window
								'list could be written to text file
								'list could be dumped to clipboard
								'list could be parsed, with each line going to
								'	its own record in a table
								'	If line length > 254 would have to use memo field

end

[Tom Cone Jr]

Re: Join multiple records (same field) into one record

Roderick, it's a mistake to use someone else's code without understanding what each line does. The other person often isn't around when something changes (or breaks!). Check the help file discussioin of <tbl>.index_primary_put(). You'll find that you need to build an index on the student_id field first, and then use the "name" or "tag" for that index in this function. The effect is to sort the table as desired. If this isn't clear let us know what remains confusing or difficult.

The best way to use code that's suggested in message board posts is to tear it apart line by line; using the help file to lookup terms that are unfamiliar. A great way to learn. -- tom

ps.
If any of the table fields include commas, you'll need to delimit the character values in the output string with quote marks. Since you've omitted the code I supplied that did this I'm assuming you have no table field values that include commas, right?

[Stan Mathews]

Re: Join multiple records (same field) into one record

tbl = table.open("Students")
tbl.query_create("N",".T.","student[COLOR="Red"]id[/COLOR]")
tbl.fetch_first()
fil = file.create("C:\upload.txt",FILE_RW_EXCLUSIVE)
WHILE .not. tbl.fetch_eof()
	osis = alltrim(tbl.Student[COLOR="red"]id[/COLOR])
	firstname = alltrim(tbl.Firstname)
	lastname = alltrim(tbl.Lastname)
	class = strtran(table.external_record_content_get("Students","alltrim(class)","","student[COLOR="red"]id[/COLOR] = "+quote(osis)),crlf(),",")
	fil.write_line(osis+","+firstname+","+lastname+","+class)
	WHILE tbl.student[COLOR="red"]id[/COLOR] = osis .and. .not. tbl.fetch_eof()
		tbl.fetch_next()
	END WHILE
END WHILE
fil.flush()
fil.close()
tbl.close()

[Roderick]

Re: Join multiple records (same field) into one record

Tom you are right. This is a great way to learn. When I look other members posts and try to anylize the code, it is a little harder to comprehend. When I look at code for one of my problems it is easier to see what is going on. Not too many Xbasic book out there.

I will look into indexing and will post my final script here.

I will also figure out how to manually add the headers into the file. I don't think that will be a problem.

Thanks for pointing me in the right direction.

Roderick

[Tom Cone Jr]

Re: Join multiple records (same field) into one record

Good. Hang in there. "Translation" of code is a bit tedious but it's a great way to learn the "language". If your wheels start spinning, pop back here and let us help. -- tom

[Roderick]

Re: Join multiple records (same field) into one record

Stan,

I had made the correct changes (my studentid is actually student). I figured out what the problem was.

When I created the operation to import the upload.txt file back into A5 and compare data, the script uses the first record and limits the number of imported fields for the rest of the records. In this case it is 14 which only allows for id,first,last, and 11 classes. That is why my second record is missing a couple of classes.

So, the script works perfectly. What I have to do now is manually add headers before I start writing the student data to the file.

That would mean I would need to:

- Loop through all the students until end of file
- While inside the student loop I would have to count the number of classes
- Before starting to count the next student's classes I compare the current student's class count to the Highest_class_count.
- If the current_class_count > highest then Highest = Current

Once that is done I need to manually add the headers: student,first,last
and then loop from highest while highest-1 does not 0

Does all of that make sense? Is there an easier way?

[Stan Mathews]

Re: Join multiple records (same field) into one record

Just fill the final table in the script instead of exporting to the csv.

tbl = table.open("Students")
tbl.query_create("N",".T.","student")
tbl.fetch_first()
tbl_dest = table.open("destinationtablename")
WHILE .not. tbl.fetch_eof()
	osis = alltrim(tbl.Student)
        class = strtran(table.external_record_content_get("Students","alltrim(class)","","student = "+quote(osis)),crlf(),",")
	tbl.dest.enter_begin()
	tbl_dest.student = tbl.student
	tbl_dest.firstname = tbl.firstname
	tbl_dest.lastname = tbl.lastname
	tbl_dest.class = class
	tbl_dest.enter_end(.t.)
	WHILE tbl.student = osis .and. .not. tbl.fetch_eof()
		tbl.fetch_next()
	END WHILE
END WHILE
tbl.close()
tbl_dest.close()

Assumes the final table has the same fieldnames as the source and the final table has been emptied before use.

[Roderick]

Re: Join multiple records (same field) into one record

tbl = table.open("Students")
tbl.query_create("N",".T.","student")
tbl.fetch_first()
tbl_dest = table.open("destinationtablename")
WHILE .not. tbl.fetch_eof()
	osis = alltrim(tbl.Student)
        class = strtran(table.external_record_content_get("Students","alltrim(class)","","student = "+quote(osis)),crlf(),",")
	[COLOR="Red"]tbl_dest.enter_begin()[/COLOR]
	tbl_dest.student = tbl.student
	tbl_dest.firstname = tbl.firstname
	tbl_dest.lastname = tbl.lastname
	tbl_dest.class = class
	tbl_dest.enter_end(.t.)
	WHILE tbl.student = osis .and. .not. tbl.fetch_eof()
		tbl.fetch_next()
	END WHILE
END WHILE
tbl.close()
tbl_dest.close()

A couple of things happened with this one. The studentIDs were all 0. More importantly, the class data was in one field.

Can the field name be a variable that we increment? class2, class3, etc.