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     [CLOSED] IF COUNT statement

Read-Only Read-Only Topic
Go
Search
Notify
Tools
[CLOSED] IF COUNT statement
 Login/Join
 
hfung1
Silver Member
posted
Hi,

I currently have a report where I am doing a COUNT of procedure volumes.

So I have a field called Intervention1, and another field called Intervention2. These two fields are simply the name of the intervention (the possible values in both fields are the same).

My report has no problem counting the number of times "A" comes up in Intervention 1 and Intervention 2. The problem I have is, Intervention 1 is the more "important" field, so if there is a value in Intervention1, I don't need to count the value in Intervention2. Basically, I only want to COUNT intervention 2 if Intervention 1 is null. Right now, I just have a WHERE clause (where intervention1 = "A" OR intervention2 = "A").

Is there anyway I can code this?

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


8.0.0.2
Windows, All Outputs
 
Posts: 41 | Registered: February 27, 2014Report This Post
susannah
Expert
posted Hide Post
sure
several ways
define a field that is exactly what you want
and then count that
DEFINE FILE ...
inter2/A1 MISSING ON = IF INTERVENTION1 EQ 'A' THEN MISSING ELSE INTERVENTION2;
END



In Focus since 1979///7706m/5 ;wintel 2008/64;OAM security; Oracle db, ///MRE/BID
 
Posts: 3811 | Location: Manhattan | Registered: October 28, 2003Report This Post
Danny-SRL
Virtuoso
posted Hide Post
I think what you want is:
  
DEFINE FILE ...
INTER2/{same format as intervantion2] MISSING ON = IF INTERVENTION1 EQ MISSING THEN INTERVENTION2 ELSE MISSING;
...
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
hfung1
Silver Member
posted Hide Post
Hmm...it's not working for me:

DEFINE FILE
MONTHNO1/A11=EDIT(FISCAL_MONTH_OF_YEAR);
MONTHNO2/A2=EDIT(MONTHNO1,'$$$$$$$$$99');
MONTHNAME/A55=MONTHNO2 | ' ' | FISCAL_MONTH_OF_YEAR_NAME;
V_AGREEMENT.INT2_PROCEDURE/A10V MISSING ON = IF V_AGREEMENT.INT1_PROCEDURE EQ MISSING THEN V_AGREEMENT.INT2_PROCEDURE ELSE MISSING;
END

TABLE FILE V_AGREEMENT
COUNT
V_AGREEMENT.ACCOUNT_NUMBER
BY LOWEST V_AGREEMENT.INT1_PROCEDURE AS 'Intervention1'
BY LOWEST V_AGREEMENT.INT2_PROCEDURE AS 'Intervention2'
WHERE J0.DIM_DATE.DATE GE '2013-04-01';
WHERE J0.DIM_DATE.DATE LE '2014-03-31';
WHERE V_AGREEMENT.INT1_PROCEDURE EQ 'A' OR V_AGREEMENT.INT2_PROCEDURE EQ 'A';


8.0.0.2
Windows, All Outputs
 
Posts: 41 | Registered: February 27, 2014Report This Post
Tamra
Guru
posted Hide Post
Hi Hugo,

Can you please clarify what you mean by "not working"? Do you get the incorrect value or an error message appears?

Thank you for clarifying.

Cheers,
Tamra Colangelo
Information Builders Customer Support Services.


WebFOCUS 8x - BI Portal, Developer Studio, App Studio, Excel, PDF, Active Formats and HTML5
 
Posts: 487 | Location: Toronto | Registered: June 23, 2009Report This Post
RSquared
Guru
posted Hide Post
What do you want to do if intervaention1 is not equal to 'A' but intervention2 is equal to 'A'?


WF 7.6.11
Oracle
WebSphere
Windows NT-5.2 x86 32bit
 
Posts: 398 | Registered: February 04, 2008Report This Post
hfung1
Silver Member
posted Hide Post
quote:
V_AGREEMENT.INT2_PROCEDURE/A10V MISSING ON = IF V_AGREEMENT.INT1_PROCEDURE EQ MISSING THEN V_AGREEMENT.INT2_PROCEDURE ELSE MISSING


Sorry, what I meant by not working was that the table still shows everything...it does not only display intervention 2 if intervention 1 is null.


8.0.0.2
Windows, All Outputs
 
Posts: 41 | Registered: February 27, 2014Report This Post
atturhari
Platinum Member
posted Hide Post
quote:
V_AGREEMENT.INT2_PROCEDURE/A10V MISSING ON = IF V_AGREEMENT.INT1_PROCEDURE EQ MISSING THEN V_AGREEMENT.INT2_PROCEDURE ELSE MISSING


Do you need both BY fields?
How would the final output look like?

May be try this,
WHERE (V_AGREEMENT.INT1_PROCEDURE EQ 'A')
OR (V_AGREEMENT.INT1_PROCEDURE EQ MISSING AND V_AGREEMENT.INT2_PROCEDURE NE MISSING);


WF 7.7.02 on Windows 7
Teradata
HTML,PDF,EXCEL,AHTML
 
Posts: 165 | Registered: September 29, 2008Report This Post
j.gross
Virtuoso
posted Hide Post
I would DEFINE:

A_COUNT/I9C= (INT1_PROCEDURE EQ 'A') OR ((INT1_PROCEDURE IS MISSING) AND (INT2_PROCEDURE EQ 'A'));

and simply

SUM A_COUNT


- Jack Gross
WF through 8.1.05
 
Posts: 1925 | Location: NYC | In FOCUS since 1983 | Registered: January 11, 2005Report This Post
RSquared
Guru
posted Hide Post
My question is what do you want to happen if INT_1 does not equal to 1 and INT_1 is not missing? Do you still want to look at INT_2?


WF 7.6.11
Oracle
WebSphere
Windows NT-5.2 x86 32bit
 
Posts: 398 | Registered: February 04, 2008Report This Post
hfung1
Silver Member
posted Hide Post
quote:
Originally posted by RSquared:
My question is what do you want to happen if INT_1 does not equal to 1 and INT_1 is not missing? Do you still want to look at INT_2?


This is what we want to show:

If INT1 = A, only show INT1
If INT1 = BLANK/Missing then go to INT2 and show INT2 = A
if INT 1 = NOT A but also NOT MISSING/BLANK, so let's say INT1 = B, it should NOT show either INT1 or INT 2

So basically, only look at INT2 if INT1 is missing/blank


8.0.0.2
Windows, All Outputs
 
Posts: 41 | Registered: February 27, 2014Report This Post
jvb
Gold member
posted Hide Post
quote:
I would DEFINE:

A_COUNT/I9C= (INT1_PROCEDURE EQ 'A') OR ((INT1_PROCEDURE IS MISSING) AND (INT2_PROCEDURE EQ 'A'));

and simply

SUM A_COUNT



As suggested above following can be used:
DEFINE FILE XYZ
A_COUNT/I9C= IF (INT1_PROCEDURE EQ 'A') OR ((INT1_PROCEDURE IS MISSING) AND (INT2_PROCEDURE EQ 'A')) THEN 1 ELSE 0;
END
TABLE FILE XYZ
PRINT A_COUNT
WHERE A_COUNT NE 0
END

This should return only the required set of records.


WF 8.1.04,Infoassist,Oracle, Excel, PDF,HTML.
 
Posts: 82 | Registered: January 06, 2014Report 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     [CLOSED] IF COUNT statement

Copyright © 1996-2020 Information Builders