Focal Point Banner
Community Center Education Summit Technical Support User Groups
Let's Get Social!

Facebook Twitter LinkedIn YouTube
Focal Point    Focal Point Forums  Hop To Forum Categories  WebFOCUS/FOCUS Forum on Focal Point     [SOLVED] Question about multiple record logic
Go
New
Search
Notify
Tools
Reply
  
[SOLVED] Question about multiple record logic
 Login/Join
 
Guru
posted
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,


Mainframe FOCUS 7.0
VM/CMS and MVS/TSO
 
Posts: 250 | Registered: January 14, 2008Reply With QuoteReport This Post
<JG>
posted
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
 
Reply With QuoteReport This Post
Guru
posted Hide Post
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!


Mainframe FOCUS 7.0
VM/CMS and MVS/TSO
 
Posts: 250 | Registered: January 14, 2008Reply With QuoteReport This Post
<JG>
posted
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'.
 
Reply With QuoteReport This Post
Virtuoso
posted Hide Post
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.


Leah
 
Posts: 1317 | Location: Council Bluffs, IA | Registered: May 24, 2004Reply With QuoteReport This Post
Virtuoso
posted Hide Post
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 Gross
WF through 8.1.05
 
Posts: 1925 | Location: NYC | In FOCUS since 1983 | Registered: January 11, 2005Reply With QuoteReport This Post
<JG>
posted
Jack, I can't see how that's going to work.
Can you give an example against the EMPLOYEE db
 
Reply With QuoteReport This Post
Virtuoso
posted Hide Post
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.


Regards,

Darin



In FOCUS since 1991
WF Server: 7.7.04 on Linux and Z/OS, ReportCaster, Self-Service, MRE, Java, Flex
Data: DB2/UDB, Adabas, SQL Server Output: HTML,PDF,EXL2K/07, PS, AHTML, Flex
WF Client: 77 on Linux w/Tomcat
 
Posts: 2298 | Location: Salt Lake City, Utah | Registered: February 02, 2007Reply With QuoteReport This Post
Guru
posted Hide Post
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!


Mainframe FOCUS 7.0
VM/CMS and MVS/TSO
 
Posts: 250 | Registered: January 14, 2008Reply With QuoteReport This Post
Virtuoso
posted Hide Post
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.


- Jack Gross
WF through 8.1.05
 
Posts: 1925 | Location: NYC | In FOCUS since 1983 | Registered: January 11, 2005Reply With QuoteReport This Post
Guru
posted Hide Post
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!


Mainframe FOCUS 7.0
VM/CMS and MVS/TSO
 
Posts: 250 | Registered: January 14, 2008Reply With QuoteReport This Post
<JG>
posted
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
 
Reply With QuoteReport This Post
Guru
posted Hide Post
Thanks JG,

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


Mainframe FOCUS 7.0
VM/CMS and MVS/TSO
 
Posts: 250 | Registered: January 14, 2008Reply With QuoteReport This Post
Virtuoso
posted Hide Post
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.


- Jack Gross
WF through 8.1.05
 
Posts: 1925 | Location: NYC | In FOCUS since 1983 | Registered: January 11, 2005Reply With QuoteReport This Post
Guru
posted Hide Post
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!


Mainframe FOCUS 7.0
VM/CMS and MVS/TSO
 
Posts: 250 | Registered: January 14, 2008Reply With QuoteReport This Post
  Powered by Social Strata  
 

Focal Point    Focal Point Forums  Hop To Forum Categories  WebFOCUS/FOCUS Forum on Focal Point     [SOLVED] Question about multiple record logic

Copyright © 1996-2018 Information Builders, leaders in enterprise business intelligence.