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]Display Blank Count Rows

Read-Only Read-Only Topic
Go
Search
Notify
Tools
[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, 2007Report 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.07OS:LinuxOutputs:HTML, PDF, Excel, PPT
In Focus since 1984
Pity the lost knowledge of an old programmer!

 
Posts: 6347 | Location: 33°49'23.0"S, 151°11'41.0"E | Registered: October 31, 2006Report 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, 2010Report 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, 2007Report 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 10 - IE11.
in Focus since 1988
 
Posts: 1961 | Location: Netherlands | Registered: September 25, 2007Report 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, 2005Report 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, 2007Report 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, 2012Report 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, 2016Report 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]Display Blank Count Rows

Copyright © 1996-2020 Information Builders