Alpha Software Mobile Development Tools:   Alpha Anywhere    |   Alpha TransForm subscribe to our YouTube Channel  Follow Us on LinkedIn  Follow Us on Twitter  Follow Us on Facebook

Announcement

Collapse

The Alpha Software Forum Participation Guidelines

The Alpha Software Forum is a free forum created for Alpha Software Developer Community to ask for help, exchange ideas, and share solutions. Alpha Software strives to create an environment where all members of the community can feel safe to participate. In order to ensure the Alpha Software Forum is a place where all feel welcome, forum participants are expected to behave as follows:
  • Be professional in your conduct
  • Be kind to others
  • Be constructive when giving feedback
  • Be open to new ideas and suggestions
  • Stay on topic


Be sure all comments and threads you post are respectful. Posts that contain any of the following content will be considered a violation of your agreement as a member of the Alpha Software Forum Community and will be moderated:
  • Spam.
  • Vulgar language.
  • Quotes from private conversations without permission, including pricing and other sales related discussions.
  • Personal attacks, insults, or subtle put-downs.
  • Harassment, bullying, threatening, mocking, shaming, or deriding anyone.
  • Sexist, racist, homophobic, transphobic, ableist, or otherwise discriminatory jokes and language.
  • Sexually explicit or violent material, links, or language.
  • Pirated, hacked, or copyright-infringing material.
  • Encouraging of others to engage in the above behaviors.


If a thread or post is found to contain any of the content outlined above, a moderator may choose to take one of the following actions:
  • Remove the Post or Thread - the content is removed from the forum.
  • Place the User in Moderation - all posts and new threads must be approved by a moderator before they are posted.
  • Temporarily Ban the User - user is banned from forum for a period of time.
  • Permanently Ban the User - user is permanently banned from the forum.


Moderators may also rename posts and threads if they are too generic or do not property reflect the content.

Moderators may move threads if they have been posted in the incorrect forum.

Threads/Posts questioning specific moderator decisions or actions (such as "why was a user banned?") are not allowed and will be removed.

The owners of Alpha Software Corporation (Forum Owner) reserve the right to remove, edit, move, or close any thread for any reason; or ban any forum member without notice, reason, or explanation.

Community members are encouraged to click the "Report Post" icon in the lower left of a given post if they feel the post is in violation of the rules. This will alert the Moderators to take a look.

Alpha Software Corporation may amend the guidelines from time to time and may also vary the procedures it sets out where appropriate in a particular case. Your agreement to comply with the guidelines will be deemed agreement to any changes to it.



Bonus TIPS for Successful Posting

Try a Search First
It is highly recommended that a Search be done on your topic before posting, as many questions have been answered in prior posts. As with any search engine, the shorter the search term, the more "hits" will be returned, but the more specific the search term is, the greater the relevance of those "hits". Searching for "table" might well return every message on the board while "tablesum" would greatly restrict the number of messages returned.

When you do post
First, make sure you are posting your question in the correct forum. For example, if you post an issue regarding Desktop applications on the Mobile & Browser Applications board , not only will your question not be seen by the appropriate audience, it may also be removed or relocated.

The more detail you provide about your problem or question, the more likely someone is to understand your request and be able to help. A sample database with a minimum of records (and its support files, zipped together) will make it much easier to diagnose issues with your application. Screen shots of error messages are especially helpful.

When explaining how to reproduce your problem, please be as detailed as possible. Describe every step, click-by-click and keypress-by-keypress. Otherwise when others try to duplicate your problem, they may do something slightly different and end up with different results.

A note about attachments
You may only attach one file to each message. Attachment file size is limited to 2MB. If you need to include several files, you may do so by zipping them into a single archive.

If you forgot to attach your files to your post, please do NOT create a new thread. Instead, reply to your original message and attach the file there.

When attaching screen shots, it is best to attach an image file (.BMP, .JPG, .GIF, .PNG, etc.) or a zip file of several images, as opposed to a Word document containing the screen shots. Because Word documents are prone to viruses, many message board users will not open your Word file, therefore limiting their ability to help you.

Similarly, if you are uploading a zipped archive, you should simply create a .ZIP file and not a self-extracting .EXE as many users will not run your EXE file.
See more
See less

Join multiple records (same field) into one record

Collapse
X
 
  • Filter
  • Time
  • Show
Clear All
new posts

  • Join multiple records (same field) into one record

    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
    Thanks,

    Roderick Silva
    rodericksilva@gmail.com

    About Me: rodericksilva.com
    Twitter: rodericksilva

  • #2
    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?

    Comment


    • #3
      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.
      Last edited by Roderick; 11-28-2009, 11:46 AM.
      Thanks,

      Roderick Silva
      rodericksilva@gmail.com

      About Me: rodericksilva.com
      Twitter: rodericksilva

      Comment


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

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

        -- tom

        Comment


        • #5
          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.
          There can be only one.

          Comment


          • #6
            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.
            Thanks,

            Roderick Silva
            rodericksilva@gmail.com

            About Me: rodericksilva.com
            Twitter: rodericksilva

            Comment


            • #7
              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?

              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()
              Last edited by Roderick; 11-29-2009, 11:53 PM. Reason: I forgot to address Stan
              Thanks,

              Roderick Silva
              rodericksilva@gmail.com

              About Me: rodericksilva.com
              Twitter: rodericksilva

              Comment


              • #8
                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:

                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
                Last edited by Roderick; 11-29-2009, 11:58 PM. Reason: added ?
                Thanks,

                Roderick Silva
                rodericksilva@gmail.com

                About Me: rodericksilva.com
                Twitter: rodericksilva

                Comment


                • #9
                  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?
                  Last edited by Tom Cone Jr; 11-30-2009, 07:32 AM.

                  Comment


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

                    Code:
                    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()
                    There can be only one.

                    Comment


                    • #11
                      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
                      Thanks,

                      Roderick Silva
                      rodericksilva@gmail.com

                      About Me: rodericksilva.com
                      Twitter: rodericksilva

                      Comment


                      • #12
                        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

                        Comment


                        • #13
                          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?
                          Thanks,

                          Roderick Silva
                          rodericksilva@gmail.com

                          About Me: rodericksilva.com
                          Twitter: rodericksilva

                          Comment


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

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

                            Code:
                            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.
                            There can be only one.

                            Comment


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

                              Code:
                              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.
                              Thanks,

                              Roderick Silva
                              rodericksilva@gmail.com

                              About Me: rodericksilva.com
                              Twitter: rodericksilva

                              Comment

                              Working...
                              X