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] Display zero counts when using DECODE

Read-Only Read-Only Topic
Go
Search
Notify
Tools
[SOLVED] Display zero counts when using DECODE
 Login/Join
 
Platinum Member
posted
Below is my code. I am trying to count records based on ranges. However when the count is zero I want to still see the range with a zero count displaying. How do I accomplish this? Thanks!

DEFINE FILE AMBPROP
PROP1/I2=
IF AMBPROP_ASK_AMOUNT GE 1000000 THEN 1
ELSE IF AMBPROP_ASK_AMOUNT GE 500000 THEN 2
ELSE IF AMBPROP_ASK_AMOUNT GE 100000 THEN 3
ELSE 4;
PROP2/A20=
DECODE PROP1( 1 '$1,000,000 and above'
2 '$500,000 - $999,999'
3 '$100,000 - $499,999'
4 '$25,000 - 99,999');
end
TABLE FILE AMBPROP
SUM
CNT.PROP1 AS 'NUMBER'
AMBPROP_ASK_AMOUNT AS 'AMOUNT'
BY PROP2 AS 'Proposals Presented'

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


tbj
Prod WF 8.1.05,Test WF 8.1.05, WINDOWS 7 Platform, Oracle 12
Excel, PDF, Alpha
 
Posts: 132 | Location: Chapel Hill, NC | Registered: October 24, 2006Report This Post
Virtuoso
posted Hide Post
after your last line add

ROWS '$1,000,000 and above' AND
'$500,000 - $999,999' AND
 '$100,000 - $499,999' AND
 '$25,000 - 99,999';





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
Does "AND" work for ROWS? I believe the correct syntax was ROWS xxx OVER or COLUMNS yyy AND unless something has changed since I last noticed (which is known to happen!)


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
Platinum Member
posted Hide Post
0 ERROR AT OR NEAR LINE 24 IN PROCEDURE ADHOCRQ FOCEXEC *
(FOC080) WORD OR SYNTAX IN 'FOR' PHRASE (FML OPTION) NOT RECOGNIZED: AND
BYPASSING TO END OF COMMAND
(FOC009) INCOMPLETE REQUEST STATEMENT


tbj
Prod WF 8.1.05,Test WF 8.1.05, WINDOWS 7 Platform, Oracle 12
Excel, PDF, Alpha
 
Posts: 132 | Location: Chapel Hill, NC | Registered: October 24, 2006Report This Post
Platinum Member
posted Hide Post
I am getting zero rows in my output, I know there is data....


DEFINE FILE AMBPROP
PROP1/I2=
IF AMBPROP_ASK_AMOUNT GE 1000000 THEN 1
ELSE IF AMBPROP_ASK_AMOUNT GE 500000 THEN 2
ELSE IF AMBPROP_ASK_AMOUNT GE 100000 THEN 3
ELSE 4;
PROP2/A20=
DECODE PROP1( 1 '$1,000,000 and above'
2 '$500,000 - $999,999'
3 '$100,000 - $499,999'
4 '$25,000 - 99,999');
MONTH/A3=HNAME(AMBPROP_PRST_DATE, 'MONTH', 'A3');
ACTDATE/MDYY=HDATE(AMBPROP_PRST_DATE, 'MDYY');
YEAR/A4=HNAME(AMBPROP_PRST_DATE, 'YEAR', 'A4');
FISC/I4=
IF ACTDATE GE 07012007 AND ACTDATE LT 07012008 THEN 2008
ELSE IF ACTDATE GE 07012008 AND ACTDATE LT 07012009 THEN 2009;
END
TABLE FILE AMBPROP
SUM
CNT.PROP1 AS 'NUMBER'
AMBPROP_ASK_AMOUNT AS 'AMOUNT'
BY PROP2 AS 'Proposals Presented'
ROWS '$1,000,000 and above' OVER
'$500,000 - $999,999' OVER
'$100,000 - $499,999' OVER
'$25,000 - 99,999';
ACROSS MONTH AS ''
WHERE AMBPROP_PRST_CODE IN ('E', '1');
ON TABLE SET PAGE-NUM OFF
ON TABLE COLUMN-TOTAL AS 'TOTAL' PROP1 AMBPROP_ASK_AMOUNT
ON TABLE PCHOLD FORMAT PDF


tbj
Prod WF 8.1.05,Test WF 8.1.05, WINDOWS 7 Platform, Oracle 12
Excel, PDF, Alpha
 
Posts: 132 | Location: Chapel Hill, NC | Registered: October 24, 2006Report This Post
Virtuoso
posted Hide Post
Darin you are right.....AND is with COLUMNS, in combination with ROWS it should be OVER.




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
No need for the semi-colon after the last value '$25,000 - 99,999'
Try commenting out everything after BY PROP2 AS 'Proposals Presented'
and maybe add a recordlimit and see if you get data. If that works, try adding pieces back in a piece at a time to determine where the problem is.


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
Tracie

did you get any output without that special line with the OVER....




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
Expert
posted Hide Post
Tracie,

Prove that you have the data in yout file, that you believe that you have, by placing this code after your DEFINE -

TABLE FILE AMBPROP
PRINT * PROP1 PROP2
END
-EXIT

Make sure PROP1 and PROP2 are populated with the values that you expect them to have.

T



In FOCUS
since 1986
WebFOCUS Server 8.2.01M, thru 8.2.07 on Windows Svr 2008 R2  
WebFOCUS App Studio 8.2.06 standalone on Windows 10 
 
Posts: 5694 | Location: United Kingdom | Registered: April 08, 2004Report This Post
Platinum Member
posted Hide Post
Yes Frank I get output without the ROW/OVER lines.

When I add it back I get nothing....


tbj
Prod WF 8.1.05,Test WF 8.1.05, WINDOWS 7 Platform, Oracle 12
Excel, PDF, Alpha
 
Posts: 132 | Location: Chapel Hill, NC | Registered: October 24, 2006Report This Post
Platinum Member
posted Hide Post
I took out the semi colon too


tbj
Prod WF 8.1.05,Test WF 8.1.05, WINDOWS 7 Platform, Oracle 12
Excel, PDF, Alpha
 
Posts: 132 | Location: Chapel Hill, NC | Registered: October 24, 2006Report This Post
Virtuoso
posted Hide Post
Instead of
BY PROP2 AS 'Proposals Presented'
ROWS '$1,000,000 and above' OVER
'$500,000 - $999,999' OVER
'$100,000 - $499,999' OVER
'$25,000 - 99,999'

try this:
BY PROP1 ROWS 1 OVER 2 OVER 3 OVER 4

just to see if you still get data. I have a little suspicion that it may be in your decoded values.

If that works, try taking out to ROWS OVER and just leave the BY PROP2 and see what BY values you get.


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
Master
posted Hide Post
I think the problem is with using ROWS OVER with ACROSS. If you remove the ACROSS or change it to a BY and leave the ROWS OVER do you get data?


Pat
WF 7.6.8, AIX, AS400, NT
AS400 FOCUS, AIX FOCUS,
Oracle, DB2, JDE, Lotus Notes
 
Posts: 755 | Location: TX | Registered: September 25, 2007Report This Post
Platinum Member
posted Hide Post
NUMBER AMOUNT
1 . .
2 . .
3 . .
4 2 559800.00

TOTAL 2 559800.00

This is what I get when I use what Darin suggested.....

PBrightwell I got no data with your suggestion


tbj
Prod WF 8.1.05,Test WF 8.1.05, WINDOWS 7 Platform, Oracle 12
Excel, PDF, Alpha
 
Posts: 132 | Location: Chapel Hill, NC | Registered: October 24, 2006Report This Post
Virtuoso
posted Hide Post
So that means that you have no data falling into sort values 1,2,3 and 2 records in value 4.
Try changing you BY statement to only read BY PROP2 and see what you get. If the values you specify in the ROWS OVER don't EXACTLY match the sort field value, you'll get no 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, 2007Report This Post
Platinum Member
posted Hide Post
Right that was my purpose to display zeros where the no data is.
I'll try it...


tbj
Prod WF 8.1.05,Test WF 8.1.05, WINDOWS 7 Platform, Oracle 12
Excel, PDF, Alpha
 
Posts: 132 | Location: Chapel Hill, NC | Registered: October 24, 2006Report This Post
Master
posted Hide Post
You're going about this wrong. You only have records for 25000 - 99999, when you decode that is all you will get the other fields do not exist for PROP2. You need to change your logic to define fields for each of the possible ranges.
 
DEFINE FILE AMBPROP
R1/I2=IF AMBPROP_ASK_AMOUNT GE 1000000 THEN 1
ELSE 0;
R2/I2=IF AMBPROP_ASK_AMOUNT GE 500000 AND LT 1000000 THEN 1 ELSE 0;
R3/I2=IF AMBPROP_ASK_AMOUNT GE 100000 AND LT 500000 THEN 1 ELSE 0;
R4/I2=IF AMBPROP_ASK_AMOUNT GE 25000 AND LT 100000 THEN 1 ELSE 0;
END
TABLE FILE AMBPROP
SUM R1 AS '$1,000,000 and above'
OVER R2 AS ' $500,000 - $999,999'
OVER R3 AS ' $100,000 - $499,999'
OVER R4 AS '  $25,000 - $100,000'
HEADING
"<+0> <+0>Number"
END


Pat
WF 7.6.8, AIX, AS400, NT
AS400 FOCUS, AIX FOCUS,
Oracle, DB2, JDE, Lotus Notes
 
Posts: 755 | Location: TX | Registered: September 25, 2007Report This Post
Platinum Member
posted Hide Post
that worked! Thanks!

Now how do I get the amount to show beside each row?

Recent Major Gift Activity

Proposals Presented

$1,000,000 and above 0
$500,000 - $999,999 0
$100,000 - $499,999 2
$25,000 - $100,000 0 AMOUNT 559800.00


tbj
Prod WF 8.1.05,Test WF 8.1.05, WINDOWS 7 Platform, Oracle 12
Excel, PDF, Alpha
 
Posts: 132 | Location: Chapel Hill, NC | Registered: October 24, 2006Report This Post
Master
posted Hide Post
DEFINE FILE AMBPROP
R1/I2=IF AMBPROP_ASK_AMOUNT GE 1000000 THEN 1
ELSE 0;
R2/I2=IF AMBPROP_ASK_AMOUNT GE 500000 AND LT 1000000 THEN 1 ELSE 0;
R3/I2=IF AMBPROP_ASK_AMOUNT GE 100000 AND LT 500000 THEN 1 ELSE 0;
R4/I2=IF AMBPROP_ASK_AMOUNT GE 25000 AND LT 100000 THEN 1 ELSE 0;
AMT1/D20.2=IF AMBPROP_ASK_AMOUNT GE 1000000  THEN AMBPROP_ASK_AMOUNT  ELSE 0;
AMT2/D20.2=IF AMBPROP_ASK_AMOUNT GE 500000 AND LT 1000000 THEN AMBPROP_ASK_AMOUNT  ELSE 0;
AMT3/D20.2=IF AMBPROP_ASK_AMOUNT GE 100000 AND LT 500000 THEN AMBPROP_ASK_AMOUNT  ELSE 0;
AMT4/D20.2=IF AMBPROP_ASK_AMOUNT GE 25000 AND LT 100000 THEN AMBPROP_ASK_AMOUNT  ELSE 0;
END
TABLE FILE AMBPROP
SUM R1 AS '$1,000,000 and above'  AMT1 AS ''
OVER R2 AS ' $500,000 - $999,999' AMT2 AS ''
OVER R3 AS ' $100,000 - $499,999' AMT3 AS ''
OVER R4 AS '  $25,000 - $100,000' AMT4 AS ''
HEADING
"<+0> <+0>Number <+0> Amount"
END


Pat
WF 7.6.8, AIX, AS400, NT
AS400 FOCUS, AIX FOCUS,
Oracle, DB2, JDE, Lotus Notes
 
Posts: 755 | Location: TX | Registered: September 25, 2007Report This Post
Platinum Member
posted Hide Post
COMPLETE! THANKS TO ALL!


tbj
Prod WF 8.1.05,Test WF 8.1.05, WINDOWS 7 Platform, Oracle 12
Excel, PDF, Alpha
 
Posts: 132 | Location: Chapel Hill, NC | Registered: October 24, 2006Report 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] Display zero counts when using DECODE

Copyright © 1996-2020 Information Builders