I've got a question or two about the proper way to pick records in a student database that have different types of test information

Students can have one or more tests, and of those tests, some of the tests have higher priority than others.

So, here is what is ultimately wanted -

Student 1
ACT Test <====== only this type of test is wanted on the output
ACCUP Test <====== discard this test, IF there is an ACT test present for the student

Student 2
SAT Test <===== only this type of test is wanted on the output

Student 3
ACT Test <====== only this type of test is wanted on the output
SAT Test <====== discard this test, IF there is an ACT test present for the student

Student 4
ACCUP Test <====== this test is wanted, since there is no ACT or SAT test
SISB1 Test <====== this test is also wanted
SISB2 Test <====== this test is also wanted

In short, if there is an ACT test for a student, take only that test and no others. If there is an SAT test but no ACT test, take only that test and no others. If there is no ACT or SAT test, then take all of the other test types.

I was thinkiong of using MAX. logic, but that wouldn't work in the case for STudent 4, above, nor would LST. lo or FST. logic. I'm looking for something like what you can do in Visual Basic or COBOL.... probably some kind of IF logic.

And yes, this is similar to another posting I've had out there, but since the logic I'm searching for is a bit different, decided to start another post.

Can anyone out there lend a helping hand?

This message has been edited. Last edited by: webmeister,

You need to use some define and compute logic, try the following

DEFINE FILE EMPLOYEE
A1/A1= IF COURSE_CODE EQ '101' THEN 'Y' ELSE 'N';
A2/A1= IF COURSE_CODE EQ '104' THEN 'Y' ELSE 'N';
END
TABLE FILE EMPLOYEE
SUM MAX.A1 NOPRINT
COMPUTE X1/A1= IF C1 EQ 'Y' THEN 'N' ELSE MAX.A2; NOPRINT
BY EMP_ID

PRINT COURSE_CODE COURSE_NAME A1
COMPUTE A4/A1= IF C1 EQ 'Y' THEN 'N' ELSE A2;
COMPUTE A5/A1= IF C1 EQ 'Y' OR C2 EQ 'Y' THEN 'N' ELSE 'Y';

BY EMP_ID
ON TABLE HOLD
ON TABLE SET HOLDLIST PRINTONLY
END
-RUN

TABLE FILE HOLD
PRINT COURSE_CODE COURSE_NAME
BY EMP_ID
WHERE A1 EQ 'Y' OR A4 EQ 'Y' OR A5 EQ 'Y'
ON TABLE PCHOLD FORMAT HTML
END

JG,

Thank you for your reply....not sure I understand it completely, but will study it further. I'm also working on my own version of that type of logic. Could you please explain what your sample is doing? The part that confused me was right here....

COMPUTE X1/A1= IF C1 EQ 'Y' THEN 'N' ELSE MAX.A2; NOPRINT (what does the X1 value do??)
BY EMP_ID

PRINT COURSE_CODE COURSE_NAME A1
COMPUTE A4/A1= IF C1 EQ 'Y' THEN 'N' ELSE A2; (what is being done on these two lines??)
COMPUTE A5/A1= IF C1 EQ 'Y' OR C2 EQ 'Y' THEN 'N' ELSE 'Y';


I appreciate your willingness to help. Thank you!

X1 is the value that is referenced as C2 (Internal WebFocus matrix reference) in the 2nd set of computes.
It needs to be set to 'N' if A1 has a value of 'Y'.

I think what you may have to do is look into using match logic. Pull your people with the SAT and ACCUP into two files, then the ACT folks into a file. Then eliminate those in the SAT and ACCUP who have an ACT and then merge the remainders back into the ACT file with match again. Sorry I can't create a code set using car file for an example, no access.

Rather than the MAX operator, use the cherry-picking phrase "BY HIGHEST (or LOWEST) 1" with a metric field reflecting the order of preference:

Assuming you have all the test results as records, with a testtype field,

DEFINE FILE ...
 METRIC/A1= IF TESTTYPE EQ 'ACT' THEN '9'
       ELSE IF TESTTYPE EQ 'SAT' THEN '8'
       . . .
       ELSE '0';
END
TABLE ...
PRINT fields-from-the-test-record
BY STUDENT-ID
BY HIGHEST 1 METRIC NOPRINT
IF METRIC GT '0'              
...
END

Jack, I can't see how that's going to work.
Can you give an example against the EMPLOYEE db

I would use Jack's code with one minor change (as is, you wouldn't get the "all other" tests.) How about this:

DEFINE FILE ...
METRIC/A1= IF TESTTYPE EQ 'ACT' THEN '2'
ELSE IF TESTTYPE EQ 'SAT' THEN '1'
ELSE '0';
END
TABLE ...
PRINT fields-from-the-test-record
BY STUDENT-ID
BY HIGHEST 1 METRIC NOPRINT
END

Explanation (which I assume matches Jack's explanation) - after the BY HIGHEST is performed, if the highest value is '2' (ACT exists) then you get that one record. If that highest value is '1' (ACT does not exist, but SAT exists) you get that one record. If highest value is '0' (neither ACT nor SAT exist) then you get ALL other test records.

JG,

Sorry for being dense...I still don't understand...I think there are too many A's X's etc. Can you possible re-write your example, using English-like terms? Such as, "If Test eq 'whatever'. I think that would help greatly in my understanding of what you are trying to show me. Thanks!

j.g.,
I've tried that approach and it does not work for what I am trying to do.

Leah,
thanks for your suggestion; I might try to work that out if all else fails.

I appreciate everyone's replies - thank you so much!

Darin --

yes, scratch the IF METRIC > 0 -- I missed the 'all other' requirement under Student 4.

By the same token as "all other" -- if the student retook the SAT (as happens) and the table includes multiple SAT records (for a particular student), HIGHEST 1 would keep them both.

j.g. and Darin,

And in this case, both results are not wanted... if there are multiples of the same test, then a max.testtype as well as max.testscore would be the way to go. However, that still does not resolve my original question / plight, which was, "In short, if there is an ACT test for a student, take only that test and no others. If there is an SAT test but no ACT test, take only that test and no others. If there is no ACT or SAT test, then take all of the other test types.

I was thinkiong of using MAX. logic, but that wouldn't work in the case for STudent 4, above, nor would LST. lo or FST. logic. I'm looking for something like what you can do in Visual Basic or COBOL.... probably some kind of IF logic."

Thanks!

Try this,

DEFINE FILE EMPLOYEE
WANT_ACT/A1= IF COURSE_CODE EQ '101' THEN 'Y' ELSE 'N';
SAT_TEST/A1= IF COURSE_CODE EQ '104' THEN 'Y' ELSE 'N';
END
TABLE FILE EMPLOYEE
SUM MAX.WANT_ACT NOPRINT
COMPUTE OVERRIDE_SAT/A1=IF C1 EQ 'Y' THEN 'N' ELSE MAX.SAT_TEST; NOPRINT
BY EMP_ID

PRINT COURSE_CODE COURSE_NAME WANT_ACT
COMPUTE WANT_SAT/A1= IF C1 EQ 'Y' THEN 'N' ELSE SAT_TEST;
-* Internal Matrix WANT_ACT=C1 OVERIDE_SAT=C2
COMPUTE WANT_OTHERS/A1= IF C1 EQ 'Y' OR C2 EQ 'Y' THEN 'N' ELSE 'Y';

BY EMP_ID
ON TABLE HOLD
ON TABLE SET HOLDLIST PRINTONLY
END
-RUN

TABLE FILE HOLD
PRINT COURSE_CODE COURSE_NAME
BY EMP_ID
WHERE WANT_ACT EQ 'Y' OR WANT_SAT EQ 'Y' OR WANT_OTHERS EQ 'Y'
ON TABLE PCHOLD FORMAT HTML
END

Thanks JG,

I'll give it a go.... I really appreciate your patience.

The cherry-picking method will work, but you need to apply it twice.

Here's sample code, using Purchase transactions in Video Track: Product represents test type (Cable purchases = ACT, blank media = SAT).

There are three sections.
(A) grabs a small sample of data.
(B) and (C) represent your report:
- (B) uses HIGHEST 1 (on a date field) to retain only the latest date's records within customer and product
- (C) then applies LOWEST 1 to a Defined metric, to yield the required set of lines.

-* For each custid, list just latest cable purchase (if any),
-* or else just latest blank medium purchase (if any)
-* or else latest purchase of each product

-* A. Pull some sample data from VideoTrack:
 JOIN CLEAR *
 JOIN PRODCODE IN VIDEOTRK TO PRODCODE IN ITEMS AS ITM:
 TABLE FILE VIDEOTRK
  PRINT  CUSTID  PRODCODE  TRANSDATE  SEG.QUANTITY
  IF CUSTID EQ 8204 OR 8771 OR 8906 OR 9001 OR 9999
  IF PRODNAME NE ' '
  ON TABLE HOLD AS HOLD1
 END

-* B. Retain just latest transaction date's records for each custid & prodcode combo:
 JOIN CLEAR *
 TABLE FILE HOLD1
  PRINT SEG.QUANTITY
  BY CUSTID
  BY PRODCODE
  BY HIGHEST 1 TRANSDATE
  ON TABLE HOLD AS HOLD2
 END

-* C. Report latest purchases as required
 JOIN CLEAR *
 JOIN PRODCODE IN HOLD2 TO PRODCODE IN ITEMS TAG ITM AS ITM:
 DEFINE FILE HOLD2
  SCORE/I1 =
       IF PRODNAME CONTAINS 'CABLE' THEN 1
  ELSE IF PRODNAME CONTAINS 'BLANK' THEN 2
                                    ELSE 9;
 END
 TABLE FILE HOLD2
  PRINT PRODNAME TRANSCODE QUANTITY TRANSTOT PRODNAME OURCOST RETAILPR ON_HAND
  BY CUSTID
  BY
    LOWEST 1
            SCORE
  BY PRODCODE
  BY TRANSDATE
 END

First run it as is.

Then you can delete the LOWEST 1 line in section C and rerun, to list the full record-set of each customer before screening on the metric -- which will show that some customers had heterogeneous scores.

j.g.,

Thanks for your response and for the sample program. It looks interesting. I'm sure withy all the information I have been provided, I will get my project off the ground.

Thanks for your help!