How to exclude certain combination pattern in data set in Webfocus?

Question

enter code hereI have a data set where I have to get rid off the student if he/she has the grade other than "F". here in an example, Brian has grades other than "F", so, Brian should not appear in my result. Only Jack should appear, as, I want EXCLUSIVELY "F" grade students.

Webfocus data

I tried it in Webfocus using OMITS, NE functions, but I am not able to resolve the issue (Please click to see the screen shot). As mentioned above, I am only expecting jack's record, as Brian has grades other than "F". I would really appreciate your help. Thanks.

I am stuck at the last line in a code:

TABLE FILE TABLE1
PRINT 
     ID
     NAME
     Grade
WHERE (ID OMITS (PRINT * from TABLE1 WHERE (Grade NE 'F')));

Answer 1

That's a very complex way of querying to WF!

This piece of code, its for loading the data you showed in the image:

-*
-* FILE ALLOCATION
-*
FILEDEF AMDEXAM DISK amdexam.ftm (APPEND
-RUN
-*
-* MASTER TO READ AMDS EXAMPLE
-*
EX -LINES 5 EDAPUT MASTER,AMDEXAM,CV,FILE
FILENAME=AMDEXAM,  SUFFIX=FIX, SEGNAME=AMDEXAM, SEGTYPE=S0, $
  FIELD=ID    ,ALIAS=ID    ,USAGE=A5  ,ACTUAL=A5  ,$
  FIELD=NAME  ,ALIAS=NAME  ,USAGE=A10 ,ACTUAL=A10 ,$
  FIELD=GRADE ,ALIAS=GRADE ,USAGE=A1  ,ACTUAL=A1  ,$
-* 
-* LOAD FILE WITH SOME RECORDS
-* 
-WRITE AMDEXAM A1   Brian     A
-WRITE AMDEXAM A1   Brian     B
-WRITE AMDEXAM A1   Brian     B
-WRITE AMDEXAM A1   Brian     A
-WRITE AMDEXAM A1   Brian     C
-WRITE AMDEXAM A1   Brian     F
-WRITE AMDEXAM A1   Brian     F
-WRITE AMDEXAM A2   Jack      F
-WRITE AMDEXAM A2   Jack      F
-WRITE AMDEXAM A2   Mystic    B
-WRITE AMDEXAM A3   Mystic    A
-WRITE AMDEXAM A3   Mystic    C
-WRITE AMDEXAM A3   Mystic    C
-WRITE AMDEXAM A4   Joe       F
-WRITE AMDEXAM A4   Joe       F
-WRITE AMDEXAM A4   Ruben     B
-WRITE AMDEXAM A4   Ruben     A
-*

You could try something like this to achieve what you've asked:

TABLE FILE AMDEXAM 
 SUM
  COMPUTE AA/I1 = IF ((LAST NAME NE NAME AND GRADE NE 'F') OR (LAST NAME EQ NAME AND LAST AA EQ 1)) THEN 1 ELSE 0; NOPRINT
  BY ID
  BY NAME
  BY GRADE
  WHERE TOTAL AA EQ 0
  ON TABLE SET BYDISPLAY ON
END
-RUN
-EXIT

That should give you the grades of Bryan.

EDIT: Changed verb from PRINT to SUM to get only one record and NOPRINT to field AA.