Focal Point Banner
Community Center Education Summit Technical Support User Groups
Let's Get Social!

Facebook Twitter LinkedIn YouTube
Focal Point    Focal Point Forums  Hop To Forum Categories  WebFOCUS/FOCUS Forum on Focal Point     [CLOSED]Display Blank Count Rows
Go
New
Search
Notify
Tools
Reply
  
[CLOSED]Display Blank Count Rows
 Login/Join
 
Platinum Member
posted
Based on my code below, I'm trying to display a summary could of records based on a group by field.

Is there a way to still display the row's label (and a 0) if the result of there are no records that meet the criteria?

So because there are no records for "One", I still need to display the row, but with a zero rather than skipping the row entirely in the display.

One | 0
Two | 1

DEFINE FILE ABCDEFG
FINAL_ID/A255= IF ID EQ '' THEN ID ELSE ID2;
FINAL_EDIT/A3= SUBSTR(255, CLR_ACR_ID,1,3,3,CLR_ACR_EDIT);
TOT_CNT/I8= 1;
END

TABLE FILE ABCDEFG
SUM
CNT.TOT_CNT AS 'TOTALREJ'
 
BY FINAL_ID

ON TABLE HOLD AS 'TOTREJCTCNT'
END

DEFINE FILE TOTREJCTCNT
TOTAL_REJ_NBR/I8= 1;
TOTAL_REJ_LBL/A255= 'One';
END

TABLE FILE TOTREJCTCNT
SUM
CNT.TOTALREJ AS 'TOTAL_ALL'

BY TOTAL_REJ_NBR
BY TOTAL_REJ_LBL

WHERE TOTALREJ EQ 1 

ON TABLE HOLD AS 'TOTREJCTCNT1'
END

DEFINE FILE TOTREJCTCNT
TOTAL_REJ_NBR/I8= 2;
TOTAL_REJ_LBL/A255= 'Two';
END

TABLE FILE TOTREJCTCNT
SUM
CNT.TOTALREJ AS 'TOTAL_ALL'

BY TOTAL_REJ_NBR
BY TOTAL_REJ_LBL

WHERE TOTALREJ EQ 2 

ON TABLE HOLD AS 'TOTREJCTCNT2'
END

TABLE FILE TOTREJCTCNT1
PRINT *

ON TABLE HOLD AS TOTREJCTALL

MORE
FILE TOTREJCTCNT2

END

TABLE FILE TOTREJCTALL
PRINT
TOTAL_ALL AS 'TOTAL_ALL'

BY TOTAL_REJ_NBR
BY TOTAL_REJ_LBL

ON TABLE PCHOLD AS EXL2K
END
-EXIT

This message has been edited. Last edited by: <Emily McAllister>,


Production - 7.6.4
Sandbox - 7.6.4
 
Posts: 241 | Location: Bethesda, MD | Registered: August 14, 2007Reply With QuoteReport This Post
Expert
posted Hide Post
You can use the ROWS statement to force the existance of records, but that only works if there is at least one record to start with.

TABLE FILE CAR
 SUM SALES
 BY COUNTRY ROWS ENGLAND OVER ITALY OVER AUSTRALIA
END


Waz...

Prod:WebFOCUS 7.6.10/8.1.04Upgrade:WebFOCUS 8.2.05OS:LinuxOutputs:HTML, PDF, Excel, PPT
In Focus since 1984
Know The Code

 
Posts: 6104 | Location: Land of the Darug people, Terra Australis Incognita | Registered: October 31, 2006Reply With QuoteReport This Post
Master
posted Hide Post
You could try something like defining:

ROWNUMBER/I2=ROWNUMBER + 1;

and then use BY ROWNUMBER NOPRINT as your first sort field.


WebFOCUS 7.7.05 Windows, Linux, DB2, IBM Lotus Notes, Firebird, Lotus Symphony/OpenOffice. Outputs PDF, Excel 2007 (for OpenOffice integration), WP
 
Posts: 674 | Location: Guelph, Ontario, Canada ... In Focus since 1985 | Registered: September 28, 2010Reply With QuoteReport This Post
Platinum Member
posted Hide Post
quote:
Originally posted by Waz:
You can use the ROWS statement to force the existance of records, but that only works if there is at least one record to start with.

TABLE FILE CAR
 SUM SALES
 BY COUNTRY ROWS ENGLAND OVER ITALY OVER AUSTRALIA
END


This works, but when you say "only works if there is at least one record", would that be why I don't get the rows where there are no results in the hold files that create the rows?

How can I force those rows where there are no results?


Production - 7.6.4
Sandbox - 7.6.4
 
Posts: 241 | Location: Bethesda, MD | Registered: August 14, 2007Reply With QuoteReport This Post
Virtuoso
posted Hide Post
Since you always know up front what rows you wish to see, you could try an alternative approach. Try this:
FILEDEF MAS       DISK COUNTRIES.MAS
FILEDEF COUNTRIES DISK COUNTRIES.FTM
-RUN
-WRITE MAS FILENAME=COUNTRIES, SUFFIX=FIX, $
-WRITE MAS SEGNAME=COUNTRIES, $
-WRITE MAS FIELDNAME=COUNTRY, FORMAT=A10, ACTUAL=A10, $
-WRITE COUNTRIES ENGLAND
-WRITE COUNTRIES ITALY
-WRITE COUNTRIES AUSTRALIA

SET ALL=ON, NODATA=0

JOIN COUNTRY IN COUNTRIES TO ALL COUNTRY IN CAR AS J1

TABLE FILE COUNTRIES
 SUM SALES
 BY COUNTRY
 ON TABLE SET EMPTYREPORT ON
 ON TABLE HOLD
END

TABLE FILE HOLD
PRINT *
END
Note: there is no real need for a hold in between, just used it to see if it also worked with a hold step. Which it does


GamP

- Using AS 8.2.01 on Windows 7 - IE11.
in Focus since 1988
 
Posts: 1960 | Location: Netherlands | Registered: September 25, 2007Reply With QuoteReport This Post
Virtuoso
posted Hide Post
If you have a data source with all the potential "key" values, you can use MATCH FILE (either coupled into your retrieval step, or afterwards) to merge the two data streams. Using OLD-OR-NEW, that will add empty rows for the missing keys, and their count will be zero.


- Jack Gross
WF through 8.1.05
 
Posts: 1925 | Location: NYC | In FOCUS since 1983 | Registered: January 11, 2005Reply With QuoteReport This Post
Platinum Member
posted Hide Post
I think I've got it working.

I've created an additional hold file that gives me every row label, and a zero for the total column.

Then I use "MORE" to union that into the actual data, and sum each column by the row label.

This gives me each row label, and either the total if there is data, or a zero if there is not.

I've got a little more testing, but I will post the code when I've got it working.

Thank you all for your help.


Production - 7.6.4
Sandbox - 7.6.4
 
Posts: 241 | Location: Bethesda, MD | Registered: August 14, 2007Reply With QuoteReport This Post
Gold member
posted Hide Post
quote:
You can use the ROWS statement to force the existance of records, but that only works if there is at least one record to start with.

TABLE FILE CAR
SUM SALES
BY COUNTRY ROWS ENGLAND OVER ITALY OVER AUSTRALIA
END



I can see how this works if you have static data (car manufacturers) how about if you have dynamic data, for example looking for the cars sold over the last 7 days by car manufacturer


WebFocus 8.02, SQL Server 2008r2
 
Posts: 51 | Registered: November 30, 2012Reply With QuoteReport This Post
Member
posted Hide Post
Hi cs_source,
For dynamic data, you need to go for Left Outer Join.
Create first hold(host file) which have all the car manufacturers list and for second hold(cross reference file) have the dataset for those cars that sold over the last 7 days by car manufacturer.Then with the Left Outer join, it will display each row of car manufacturer.

Thanks

WebFOCUS
8
 
Posts: 13 | Registered: May 31, 2016Reply With QuoteReport This Post
  Powered by Social Strata  
 

Focal Point    Focal Point Forums  Hop To Forum Categories  WebFOCUS/FOCUS Forum on Focal Point     [CLOSED]Display Blank Count Rows

Copyright © 1996-2018 Information Builders, leaders in enterprise business intelligence.