Parsing out data

[lesterd]

Problem

I just received over 10,000 student scores from a state required test. The scores go back to 1997 to 2002. There would not be a problem putting the scores into a field except some students pass the test the first time and others may try to pass the test a 8 different times. (with a different date for the test taken.

I need to create a loop or way to mark each test with a seperate mark for each attempt to pass the test. I would like to use an "a" for the first time the student took the test, a "b" for the second time, etc. I also have indicators for a pass-faill. If the student passed the test on the date the record gets a "ps" if failed, "xs"

I think there should be a way to check the student's ID#, is this the first or only id # for this student, mark it with an "a". Is there another score for this ID? mark the next one, next test date, with a "b", a third mark it with a "c".

I hope this makes sense. OH, I also want the data to go in chronological order starting with the older test for each student - oldest or only date date marked with the "a"

Thanks

Lester Drankwalter

[email protected]

[Razumovsky]

You could do this pretty easily with a loop script.

create 2 global fields, g_StudentID, g_Attempt#, and one number field n_Attempt#

find all your records and sort by studentID and Date (in that order)

script:

go to rec/req first

Set Field g_attempt#(1)

set field g_studentID (StudentID)

set field n_attempt# (g_attempt#)

loop

go to rec/req next (exit after last)

if (studentID=g_StudentID)

set field g_attempt# (g_attempt#+1)

set field n_attempt# (g_attempt#)

else

Set Field g_attempt#(1)

set field g_studentID (StudentID)

set field n_attempt# (g_attempt#)

end if

end loop

If you really need to have the succeeding test attempts as "a, b, c, d..."

you could just replace a new field, t_AlphaAttempt#, in all records with

case(n_attempt# = 1, "A",

n_attempt# = 2, "B",

n_attempt# = 3, "C",...)

-Raz

[spb]

Make yourself a layout for clarity that is a list with student name, ID number (I presume you have one, otherwise if you have two students named Steve Brown, their scores would get scrambled--with a name like mine, I'm sensitive to this problem!), date test given and a new text field, call it TestAge. Search for duplicates in your ID field with an exclamation point. This will give you all students who have taken the test more than once. Show Omitted. Now you have a set of all students who have taken the test *only* once. Run a Replace with calculation in the TestAge field, where the calc is "A".

Make an empty clone of your database, and import in all TestAge A records. Delete them from the main database (you should be working on a backup copy!). Now your main database contains only students who have taken the test two or more times. (I assume most students take it once, and this is an easy way to quickly deal with the bulk of them).

Make a new number field, TestCounter (temporary), a global counter, g_counter, and a global field, g_ID. Sort all these two or more students by ID number as primary and date test taken as secondary. Now you can make a looping script something like:

Go to Record/Request/Page (first)

Set Field (g_ID, ID)

Set Field (g_counter, 2)

Set Field (TestCounter, 2)

Loop

Go to Record/Request/Page (next) ##Check the exit after last box.

If (ID = g_ID,

Set Field (TestCounter, g_counter + 1)

Set Field (g_counter, TestCounter)

Else

Set Field (g_ID, ID)

Set Field (g_counter, 2)

Set Field (TestCounter, 2)

End If

End Loop

I'm hoping someone else will chime in and correct my syntax, if faulty. But you will be looping through all records and setting the counter for 2, 3, 4, etc., numbering, in order, the test dates by student. Now run a replace with calculation back into your original TestAge field from the TestCounter field, something like;

Case (TestCounter = 2, "B",

Testcounter = 3, "C",

Testcounter = 4, "D",

Testcounter = 5, "E",

Testcounter = 6, "F",

Testcounter = 7, "G",

"H")

That will put letter values for the numbers back in TestAge (if you could figure out the maximum number of times a student took the test, then you could set this Case up correctly the first time. You said 8, so I used 8, the eighth is the default setting after 2 through 7 have been accounted for).

If this is a one-time cleanup, delete the globals & TestCounter fields & import back in all the one-time test takers from the clone.

There you have it. This may take a while to run with 100,000 records, which is why i suggested pulling out all the one-timers first. They are easy to deal with & will probably be the bulk. It's a lot quicker to loop through 15,000 records than 100,000! you might also put a Freeze Window command as your first script command, to avoid watching it flicker through the records. This will speed it up quite a bit, as well.

As I said, I'm not absolutely certain about the script syntax. Make yourself a clone & import a couple of dozen records, then simulate as much variety as possible and try out this process. If it all works, then apply global fields & scripts to the main db.

Steve Brown

[Ugo DI LUCA]

Keeping Steve idea of a cloned db...

Create this file, and add to it 5 fields :

create a self relationship on Student_ID, call it selfonStudent_ID

1. n_Serial Number (autoentered number)

2. c_Next serial Number = calc = Max (selfonStudent_ID::serialNum)

back to serial Number, define it as a lookup from Next serial Number using the selfjoin, (use 1 if not found).

3. c_Attempt_per_Student = n_Serial Number

4. c_countertoletter =Case (c_Attempt_per_Student = 1, "A",

c_Attempt_per_Student = 2, "B",

c_Attempt_per_Student = 3, "C",

c_Attempt_per_Student = 4, "D",...

5. c_Constant(calc) = 1(indexed)

Back in your Original File, create :

1.c_Constant(calc) = 1

2.A relationship called OnetoOne matching c_constant to c_constant.

3.global g_Student_ID (number)

4.global g_RS (text) and eventually g_Date (see at the end of post)

5. t_called lettercounter (text)

Create the scripts

in Original File :

"set globals" :

setfield g_ID (student_ID)

setfield g_RS (RS)

setfield g_Date (Date) eventually

in Clone File

"set fields by globals"

setfield student ID (OnetoOne ::g_ID)

setfield RS (OnetoOne ::g_RS)

setfield Date (OnetoOne ::g_Date) eventually

in Clone File :

"new record"

in Original File :

Browse Mode

Sort (define your sorting based on 1.Student_ID - 2. RS)

Go to first record

Loop

Set Globals

Perform external "New record"

Perform external "set fields by globals"

Go to next record (end of script when last)

End Loop

Once executed, this script will give you in the Clone file a A for first attempt, a B for second, sorted by Student_ID and first PS (as your Original file found set).

Then :

Just import the datas from clone to Original File, matching "c_countertoletter" to "lettercounter".

or

Create a relationship and define lettercounter as a lookup. You would have to create a concanated field in both Original and Clone File using for example Student_ID&Date as the key.

Of course back up your file first.

[Razumovsky]

Perhaps I'm missing the obvious here, but why complicate things with a cloned database and import/export steps? What are the benefits?

I like the idea of limiting the # of records to loop through by running a find/omit for "!" duplicates, but why not just run the loop script on the found set of duplicates? and run a mass replace on the found set of non duplicates?

-Raz

[Ugo DI LUCA]

You are right Raz, this would be the simpliest way.

Honestly, I often take some of the posts on this forum as exercices. As I have some Imports to make in the next days, and as I wanted to test the serial numbers technique, I took the time for it. And as there had been already 2 posts for this thread, I assumed Elmer would have choosen the solution that fits his needs.

This exercice was totally benefit for me as I discovered that when you import the datas to a file set with serial numbers based on relationships (1-1,1-2,1-3), it seems that it doesn't work because the lookup used from selfjoin to the next serial number doesn't work while importing.

Rereading my post moreover (but that is due to my english misunderstanding, so please excuse me), I thought Lester wanted to sort the records from the first success attempt to pass the test ("Took the test", "passed the test" is my misunderstanding), when he just wanted to sort by dates. That is why my sorting was based on RS (Result) instead of Dates.

Now, as Lester told he just received this file, and if he just wanted to import some records from this file to another, the Import structure could also have been a solution.

[spb]

My original thought in using a clone was to save time. The simplest case is that of a student who took the test only once. In these, a simple replace to fill the field with "A" would suffice, no looping necessary. Assuming you are working with a lot of data, it'd take a loop a long time to grind through everything. If the single-test group is 90% of your data, pulling them out into a clone temporarily would greatly reduce the running time of the loop script. Though you probably could just run the loop against a found set of dupes.

I did notice that I screwed up in my looping script. I said to start everything with "2" and "B". However, the set of dupes will, of course, include many students' first attempt at the test. So if you want to try it, just drop down to "1" and "A" wherever it's referred to.

Ugo's try is quite elegant, maybe better. All this is based on your students having a unique ID, which I assume. Also that the test date data is defined as "date" in a date field (so it will sort correctly).

I have been assuming that this is a one-time data cleanup process. If you will be needing to repeat the process in the futre, then a clone might be unnecessarily cumbersome & more attention should be paid to scripting.

Steve Brown

[Lee Smith]

Hi Steve,

--snip

[color:"blue"]

> Ugo's try is quite elegant, maybe better. All this is based on your students

> having a unique ID, which I assume. Also that the test date data is defined

> as "date" in a date field (so it will sort correctly).

>

--snip--

Actually, the date field (called DATE) in the attachment was a text field, and needed to be converted. I was going to write back yesterday and point this out, but got busy with other stuff.

Anyway, here is the calculation needed to correct the Date information.

Create a new field and make it a calculation with result Date.

Date(Middle(DATE, 5, 2), Middle(DATE, 7, 2), Left(DATE, 4))

This is setting the date to Month, Day, Year. If it needs to be in a different order, you will need to adjust this calculation to match that order.

HTH

Lee

[Ugo DI LUCA]

Hi Lee, Hi Steve,

I was sure to have quoted this date problem. That was the point of the * "go to the end of the post". That surely makes a big difference.

Actually, the simpliest method, using the three serial fields created, would have been to import the datas into the clone, without any need for a loop script afterwards. In my mind, when I started my answer, this was obvious and simple. I just cannot figure out why this method didn't work on Lester's attachment: The serials (that need to lookup to the next) had a strange behaviour, and I then used the loop script as Raz and Steve.

Would have to check again why this was impossible.

But on the other hand, I misunderstoof Lester's wish for a sort by attempt date, as I figured out he wanted to see in the first record (a) the success, and then listed from b to z... all other attemps to pass the test.

Thank you for the "elegance" though... Have a nice evening...

[Lee Smith]

Hi Ugo,

Sorry If I missed the date thing in your post. The only reason I brought it up, was that Steve had mentioned how important that it was that the DATE field be a date field.

If anyone is interested, I ran a test on Lester's file and using Raz's approach, and IMHO it worked really well. It took just at 5 minutes to change update the file of 10,829 records. Beige G3, 233 MHz

HTH

Lee

[Razumovsky]

Hi all,

Speaking of excersises, I thought of another way to do the whole thing without looping using a variant of AndyGaunts markduplicate calc (I like that calc alot, and knew that it would come in handy often...)

1 relationship:

SelfJoin StudentID

2 fields:

n_Serial#

t_Attempt#

sort by student and date

replace n_Serial# with sequential numbers from 1

replace t_attempt# with:

case(

case(SelfJoin::TestDate= TestDate, 1, n_serial# - SelfJoin::n_serial# + 1)=1, "a",

case(SelfJoin::TestDate= TestDate, 1, n_serial# - SelfJoin::n_serial# + 1)=2, "b",

case(SelfJoin::TestDate= TestDate, 1, n_serial# - SelfJoin::n_serial# + 1)=3, "c"...)

The calc looks a bit complicated because I included the convert to alphabet step at the same time. to just get a numeric attempt #, the 2nd replace would be:

case(SelfJoin::TestDate= TestDate, 1, n_serial# - SelfJoin::n_serial# + 1)

I imagine there must be a way to do this without the first replace, but I couldnt figure it out yet.

-Raz

[lesterd]

Thanks to all who responded to my problem. It worked and I placed all the data in the record keeping system.

Lester

[Ugo DI LUCA]

More exercice....

After using Lee's formula (Lee, you didn't missed it, I forgot to put it at the end of my first post !!) for the Date and a sort by Student_ID and Date in the Original file, create a clone file (sorry for that Raz , you can call it obstination, or simply "pest" behaviour )

Create 2 relationships :

Seljoin_by_Student_ID = Seljoin S

Seljoin_by_Exam (M) = Seljoin E

Add 2 fields :

1. Serial_N