Hi, Does anyone know how to only return a line of data when the total lines within a BY grouping includes certain criteria? I know how to do it in SQL but not Focus. (similar to a subquery) i.e. If, from a list of names from the U.S., "Eric" is included, then return "U.S." Otherwise, "U.S." is not returned in the dataset. I really appreciate any help!

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

You could do a compute of a flag that is set to 1 when the criteria is met. Then do a WHERE TOTAL FLAG EQ 1.

I think that will get you what you need.

And, as a reminder, please update your profile signature with your products, releases, and platforms so that we can better help you. Some answers differ based on release.

quote:

when the total lines within a BY grouping includes certain criteria

It's nice when the question is the answer.

Ginny is so kind.

The reporting language has an INCLUDES operator. But to use it the data must be hierarchical, with the field you are testing located at a lower level than the fields you are reporting:

TABLE FILE CAR
PRINT CAR BY COUNTRY
IF SEATS INCLUDES 5
END

The field you test may be Defined (provided it is based on a lower-segment field):

DEFINE FILE CAR
  HIT/I1 = WARRANTY CONTAINS '6' OR WARRANTY CONTAINS ' ON ';
END
TABLE FILE CAR
  PRINT CAR BY COUNTRY
  IF HIT INCLUDES 1
END

Thanks Jack and all for trying to help. Unfortunately, none of these suggestions are working. Is 'Contains' available in 7.1.4? It doesn't appear to be working properly...

Have you run Jack's test in your environment. It works fine in 7.6.5.

Can you post your code?

And, as a reminder, please update your profile signature with your products, releases, and platforms so that we can better help you. Some answers differ based on release.

Yes. I've tried Jack's suggestion and it doesn't work so I'm wondering if it is because I'm using 7.1.4

Can you please post your code and update your signature?

If we saw your code, we might be able to figure out what you are doing incorrectly.

This is the code:

DEFINE FILE VIEW1
M_NO_R/I5=IF (STATUS CONTAINS 'M' AND STATUS CONTAINS 'R') THEN 1 ELSE 0;
END

TABLE FILE VIEW1
PRINT
M_NO_R
ADDRESS_ID
BY CTRY_ID
IF M_NO_R INCLUDES 1
END

I'll update my signature shortly.
Thanks!

DEFINE FILE CAR
M_NO_R/I5=IF (COUNTRY CONTAINS 'A' AND COUNTRY CONTAINS 'L') THEN 1 ELSE 0;
END

TABLE FILE CAR
PRINT COUNTRY 
M_NO_R
IF M_NO_R EQ 1
END

Change the INCLUDES to EQ and your program will work.

That didn't work either

Then print out STATUS and comment out the IF to make sure that your data has in it what you want.

Post a couple of examples.

I am curious about your column name, M_NO_R. Do you want the STATUS if it contains an 'M' but not an 'R'? If yes, you'll have to change the 2nd CONTAINS to an OMITS.

are you sue your STATUS field can contain 'M' and 'R'?
what is the format of this field?
do you mean 'M' OR 'R'?

Could this be what you are striving for? It gives the list of COUNTRYs where at least one of the STANDARD fields contains BRAKE somewhere:

  
-* File ValueInList.fex
TABLE FILE CAR
PRINT
     STANDARD
BY COUNTRY
END

DEFINE FILE CAR
HASBRAKES/I3=IF STANDARD CONTAINS 'BRAKE' THEN 1 ELSE 0;
END
TABLE FILE CAR
SUM HASBRAKES NOPRINT
BY COUNTRY
IF TOTAL HASBRAKES GT 0
END

Sorry but none of the suggestions worked. I only want to return the BY value when the subset of data (under the BY) contains 'M' OR 'R'. Thanks for all your help. Any other suggestions?

So it appears that you have answered your own question. You state that the by field must contain 'M' OR 'R' but your code says that it must contain 'M' AND 'R'.

So change your code to reflect that

DEFINE FILE VIEW1
M_NO_R/I5=IF (STATUS CONTAINS 'M' OR STATUS CONTAINS 'R') THEN 1 ELSE 0;
END

TABLE FILE VIEW1
SUM
M_NO_R
BY CTRY_ID
PRINT
ADDRESS_ID
BY CTRY_ID
WHERE TOTAL M_NO_R GE 1;
END

This message has been edited. Last edited by: Darin Lee,

Do you read the answers you get?

I posted an answer two days ago....

quote:

are you sue your STATUS field can contain 'M' and 'R'?
what is the format of this field?
do you mean 'M' OR 'R'?

And you say "none of the suggestions worked....."

Maybe mine did.

Hi,
For those who inquired, yes I do read my messages and attempt to use each of the suggestions. Otherwise, I'd be wasting your time as well as mine using Focal Point.

This is the code that I'm currently using and it doesn't return only those 'ITEM's where the 'ITEM' has at least one record where the 'STATUS' contains 'M' and there aren't any records where the 'STATUS' is 'R'. That is what I'm looking to develop. GinnyJakes suggestion to use OMITS did help specify the criteria so thank you GinnyJakes!

DEFINE FILE AIM_VW_ANT
M_NO_R/I5=IF (STATUS CONTAINS 'M' AND STATUS OMITS 'R') THEN 1 ELSE 0;
END

TABLE FILE AIM_VW_ANT
SUM
CNT.DST.ITEM
BY AREA_ID
BY RGN_ID
BY ITEM
IF M_NO_R EQ 1
ON TABLE SET PAGE-NUM OFF
ON TABLE NOTOTAL
ON TABLE SET HTMLCSS ON
END

What are the STATUS values of the additional records you are getting that you do NOT want?

It appears that the problem you are having is because you are trying to apply your criteria to the aggregation of your data (has at least one record where the 'STATUS' contains 'M' and there aren't any records where the 'STATUS' is 'R') but your DEFINE applies the logic on a record by record basis. The STATUS value in one record is not related the the STATUS value in any other records.

Your last example LOOKS like it should work but it may require a WHERE TOTAL which evaluates the summed value of all records for that sort group as opposed to your IF which only evaluates on each record without regard to any other record.

Hi Darin, Thanks for your response. The other values in the 'STATUS' field are: blank,1,A,D,I,P,S,U,and V.

Do you think WHERE TOTAL will work?

Thanks!

Actually, I just saw that GinnyJakes suggested a WHERE TOTAL earlier. Maybe I had something incorrect when I initially tried it? I'll try it again and post the code if it doesn't work again.
Thanks!

It appears that all the values are A1 format. Is there a reason you use CONTAINS and OMITS instead of EQ and NE. The latter are MUCH more efficient.

You only use WHERE TOTAL if you set the flag in a COMPUTE.

Now I am totally confused as to what you want. How about a decision table. Please indicate which combination you want STATUS to contain, a 1 means yes, 0 no. And how many characters is the field STATUS?

M R
1 1
1 0
0 1
0 0

Please advise as to which combination(s) you want STATUS to contain (1 to 4) and then we can write the appropriate code.

You might try separating your criteria like this.

DEFINE FILE AIM_VW_ANT
R_COUNT/I5=IF STATUS EQ 'R' THEN 1 ELSE 0;
M_COUNT/I5=IF STATUS EQ 'M' THEN 1 ELSE 0;
END

TABLE FILE AIM_VW_ANT
SUM
CNT.DST.ITEM
R_COUNT NOPRINT
M_COUNT NOPRINT
BY AREA_ID
BY RGN_ID
BY ITEM
WHERE TOTAL R_COUNT EQ 0;
WHERE TOTAL M_COUNT NE 0;
ON TABLE SET PAGE-NUM OFF
ON TABLE NOTOTAL
ON TABLE SET HTMLCSS ON
END

This will be effective on the lowest sort level (ITEM).

This message has been edited. Last edited by: Darin Lee,

Here's a little example of what I was thinking against CAR. Show all country, car, model for each country that offers at least one model that is a sedan but offers no models that are roadsters.

DEFINE FILE CAR
S_CNT/I5=IF BODYTYPE EQ 'SEDAN' THEN 1 ELSE 0;
R_CNT/I5=IF BODYTYPE EQ 'ROADSTER' THEN 1 ELSE 0;
END
TABLE FILE CAR
SUM
CNT.MODEL
BY COUNTRY
WHERE TOTAL S_CNT NE 0;
WHERE TOTAL R_CNT EQ 0;
PRINT CAR MODEL
BY COUNTRY
END

Excellent! Thanks Darin Lee. Your last suggestion works!