Focal Point Banner


As of December 1, 2020, Focal Point is retired and repurposed as a reference repository. We value the wealth of knowledge that's been shared here over the years. You'll continue to have access to this treasure trove of knowledge, for search purposes only.

Join the TIBCO Community
TIBCO Community is a collaborative space for users to share knowledge and support one another in making the best use of TIBCO products and services. There are several TIBCO WebFOCUS resources in the community.

  • From the Home page, select Predict: WebFOCUS to view articles, questions, and trending articles.
  • Select Products from the top navigation bar, scroll, and then select the TIBCO WebFOCUS product page to view product overview, articles, and discussions.
  • Request access to the private WebFOCUS User Group (login required) to network with fellow members.

Former myibi community members should have received an email on 8/3/22 to activate their user accounts to join the community. Check your Spam folder for the email. Please get in touch with us at community@tibco.com for further assistance. Reference the community FAQ to learn more about the community.


Focal Point    Focal Point Forums  Hop To Forum Categories  WebFOCUS/FOCUS Forum on Focal Point     [SOLVED] count if exists

Read-Only Read-Only Topic
Go
Search
Notify
Tools
[SOLVED] count if exists
 Login/Join
 
Silver Member
posted
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,


WebFOCUS 7.6.10, Windows Vista, Oracle, Output-Excel/PDF/HTML
 
Posts: 39 | Registered: September 20, 2007Report This Post
Expert
posted Hide Post
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.


Ginny
---------------------------------
Prod: WF 7.7.01 Dev: WF 7.6.9-11
Admin, MRE,self-service; adapters: Teradata, DB2, Oracle, SQL Server, Essbase, ESRI, FlexEnable, Google
 
Posts: 2723 | Location: Ann Arbor, MI | Registered: April 05, 2006Report This Post
<JG>
posted
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.
 
Report This Post
Virtuoso
posted Hide Post
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


- Jack Gross
WF through 8.1.05
 
Posts: 1925 | Location: NYC | In FOCUS since 1983 | Registered: January 11, 2005Report This Post
Silver Member
posted Hide Post
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...


WebFOCUS 7.6.10, Windows Vista, Oracle, Output-Excel/PDF/HTML
 
Posts: 39 | Registered: September 20, 2007Report This Post
Expert
posted Hide Post
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.


Ginny
---------------------------------
Prod: WF 7.7.01 Dev: WF 7.6.9-11
Admin, MRE,self-service; adapters: Teradata, DB2, Oracle, SQL Server, Essbase, ESRI, FlexEnable, Google
 
Posts: 2723 | Location: Ann Arbor, MI | Registered: April 05, 2006Report This Post
Silver Member
posted Hide Post
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


WebFOCUS 7.6.10, Windows Vista, Oracle, Output-Excel/PDF/HTML
 
Posts: 39 | Registered: September 20, 2007Report This Post
Expert
posted Hide Post
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.


Ginny
---------------------------------
Prod: WF 7.7.01 Dev: WF 7.6.9-11
Admin, MRE,self-service; adapters: Teradata, DB2, Oracle, SQL Server, Essbase, ESRI, FlexEnable, Google
 
Posts: 2723 | Location: Ann Arbor, MI | Registered: April 05, 2006Report This Post
Silver Member
posted Hide Post
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!


WebFOCUS 7.6.10, Windows Vista, Oracle, Output-Excel/PDF/HTML
 
Posts: 39 | Registered: September 20, 2007Report This Post
Expert
posted Hide Post
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.


Ginny
---------------------------------
Prod: WF 7.7.01 Dev: WF 7.6.9-11
Admin, MRE,self-service; adapters: Teradata, DB2, Oracle, SQL Server, Essbase, ESRI, FlexEnable, Google
 
Posts: 2723 | Location: Ann Arbor, MI | Registered: April 05, 2006Report This Post
Silver Member
posted Hide Post
That didn't work either Frowner


WebFOCUS 7.6.10, Windows Vista, Oracle, Output-Excel/PDF/HTML
 
Posts: 39 | Registered: September 20, 2007Report This Post
Expert
posted Hide Post
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.


Ginny
---------------------------------
Prod: WF 7.7.01 Dev: WF 7.6.9-11
Admin, MRE,self-service; adapters: Teradata, DB2, Oracle, SQL Server, Essbase, ESRI, FlexEnable, Google
 
Posts: 2723 | Location: Ann Arbor, MI | Registered: April 05, 2006Report This Post
Expert
posted Hide Post
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.


Ginny
---------------------------------
Prod: WF 7.7.01 Dev: WF 7.6.9-11
Admin, MRE,self-service; adapters: Teradata, DB2, Oracle, SQL Server, Essbase, ESRI, FlexEnable, Google
 
Posts: 2723 | Location: Ann Arbor, MI | Registered: April 05, 2006Report This Post
Virtuoso
posted Hide Post
are you sue your STATUS field can contain 'M' and 'R'?
what is the format of this field?
do you mean 'M' OR 'R'?




Frank

prod: WF 7.6.10 platform Windows,
databases: msSQL2000, msSQL2005, RMS, Oracle, Sybase,IE7
test: WF 7.6.10 on the same platform and databases,IE7

 
Posts: 2387 | Location: Amsterdam, the Netherlands | Registered: December 03, 2006Report This Post
Virtuoso
posted Hide Post
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


Daniel
In Focus since 1982
wf 8.202M/Win10/IIS/SSA - WrapApp Front End for WF

 
Posts: 1980 | Location: Tel Aviv, Israel | Registered: March 23, 2006Report This Post
Silver Member
posted Hide Post
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?


WebFOCUS 7.6.10, Windows Vista, Oracle, Output-Excel/PDF/HTML
 
Posts: 39 | Registered: September 20, 2007Report This Post
Virtuoso
posted Hide Post
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,


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, 2007Report This Post
Virtuoso
posted Hide Post
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.




Frank

prod: WF 7.6.10 platform Windows,
databases: msSQL2000, msSQL2005, RMS, Oracle, Sybase,IE7
test: WF 7.6.10 on the same platform and databases,IE7

 
Posts: 2387 | Location: Amsterdam, the Netherlands | Registered: December 03, 2006Report This Post
Silver Member
posted Hide Post
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


WebFOCUS 7.6.10, Windows Vista, Oracle, Output-Excel/PDF/HTML
 
Posts: 39 | Registered: September 20, 2007Report This Post
Virtuoso
posted Hide Post
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.


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, 2007Report This Post
Silver Member
posted Hide Post
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!


WebFOCUS 7.6.10, Windows Vista, Oracle, Output-Excel/PDF/HTML
 
Posts: 39 | Registered: September 20, 2007Report This Post
Silver Member
posted Hide Post
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!


WebFOCUS 7.6.10, Windows Vista, Oracle, Output-Excel/PDF/HTML
 
Posts: 39 | Registered: September 20, 2007Report This Post
Virtuoso
posted Hide Post
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.


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, 2007Report This Post
Expert
posted Hide Post
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.


Ginny
---------------------------------
Prod: WF 7.7.01 Dev: WF 7.6.9-11
Admin, MRE,self-service; adapters: Teradata, DB2, Oracle, SQL Server, Essbase, ESRI, FlexEnable, Google
 
Posts: 2723 | Location: Ann Arbor, MI | Registered: April 05, 2006Report This Post
Virtuoso
posted Hide Post
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,


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, 2007Report This Post
Virtuoso
posted Hide Post
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


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, 2007Report This Post
Silver Member
posted Hide Post
Excellent! Thanks Darin Lee. Your last suggestion works!


WebFOCUS 7.6.10, Windows Vista, Oracle, Output-Excel/PDF/HTML
 
Posts: 39 | Registered: September 20, 2007Report This Post
  Powered by Social Strata  

Read-Only Read-Only Topic

Focal Point    Focal Point Forums  Hop To Forum Categories  WebFOCUS/FOCUS Forum on Focal Point     [SOLVED] count if exists

Copyright © 1996-2020 Information Builders