 |
|||||
|
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
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 WebFOCUS/FOCUS Forum on Focal Point [SOLVED] Display zero counts when using DECODE
Focal Point Forums WebFOCUS/FOCUS Forum on Focal Point [SOLVED] Display zero counts when using DECODE Read-Only TopicGo | Search | Notify | Tools |
| [SOLVED] Display zero counts when using DECODE | Login/Join |
| Platinum Member |
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 | ||
|
| Virtuoso |
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';
| |||||||
|
| Virtuoso |
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 | |||
|
| Platinum Member |
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 | |||
|
| Platinum Member |
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 | |||
|
| Virtuoso |
Darin you are right.....AND is with COLUMNS, in combination with ROWS it should be OVER.
| |||||||
|
| Virtuoso |
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 | |||
|
| Virtuoso |
Tracie did you get any output without that special line with the OVER....
| |||||||
|
| Expert |
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
| |||||||||
|
| Platinum Member |
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 | |||
|
| Platinum Member |
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 | |||
|
| Virtuoso |
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 | |||
|
| Master |
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 | |||
|
| Platinum Member |
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 | |||
|
| Virtuoso |
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 | |||
|
| Platinum Member |
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 | |||
|
| Master |
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 | |||
|
| Platinum Member |
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 | |||
|
| Master |
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 | |||
|
| Platinum Member |
COMPLETE! THANKS TO ALL! tbj Prod WF 8.1.05,Test WF 8.1.05, WINDOWS 7 Platform, Oracle 12 Excel, PDF, Alpha | |||
|
| Powered by Social Strata |
 | Please Wait. Your request is being processed... |
Read-Only TopicFocal Point Focal Point Forums WebFOCUS/FOCUS Forum on Focal Point [SOLVED] Display zero counts when using DECODE
Focal Point Forums WebFOCUS/FOCUS Forum on Focal Point [SOLVED] Display zero counts when using DECODECopyright © 1996-2020 Information Builders
| View $GS_USERNAME's Public Profile |
| Add $GS_USERNAME to my Ignore List |
| View Recent Posts by $GS_USERNAME |
| Notify me of New Posts by $GS_USERNAME |