[SOLVED] Display zero counts when using DECODE

October 27, 2008, 03:30 PM

Tracie Jones

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,

October 27, 2008, 04:16 PM

FrankDutch

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';

October 27, 2008, 04:48 PM

Darin Lee

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

October 27, 2008, 04:49 PM

Tracie Jones

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

October 27, 2008, 04:53 PM

Tracie Jones

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

October 27, 2008, 04:55 PM

FrankDutch

Darin you are right.....AND is with COLUMNS, in combination with ROWS it should be OVER.

October 27, 2008, 05:02 PM

Darin Lee

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

October 27, 2008, 05:06 PM

FrankDutch

Tracie

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

October 28, 2008, 03:43 AM

Tony A

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

October 29, 2008, 12:59 PM

Tracie Jones

Yes Frank I get output without the ROW/OVER lines.

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

October 29, 2008, 01:02 PM

Tracie Jones

I took out the semi colon too

October 29, 2008, 01:35 PM

Darin Lee

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

October 30, 2008, 09:45 AM

PBrightwell

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?

October 30, 2008, 12:22 PM

Tracie Jones

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

October 30, 2008, 03:40 PM

Darin Lee

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

October 30, 2008, 03:51 PM

Tracie Jones

Right that was my purpose to display zeros where the no data is.
I'll try it...

October 31, 2008, 12:02 PM

PBrightwell

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

November 03, 2008, 10:28 AM

Tracie Jones

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

November 03, 2008, 10:41 AM

PBrightwell

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

	*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

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