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     [SOLVED] Display all possible values on COUNT
Go
New
Search
Notify
Tools
Reply
  
[SOLVED] Display all possible values on COUNT
 Login/Join
 
Member
posted
Hello,

When displaying data via COUNT, I would like to display all possible values even if they are 0.

Example:

TABLE FILE CARS
COUNT
MODEL AS 'COUNT'
BY YEAR
ON TABLE PCHOLD FORMAT HTML
END

Currently the output looks like this:

YEAR COUNT
2008 2
2013 1
2014 6
2015 2

I would like it to appear like this:

YEAR COUNT
2008 2
2009 0
2010 0
2011 0
2012 0
2013 1
2014 6
2015 2

I'm stuck here because obviously WF doesn't know all the possible options since it is only examining the actual data. How would I go about this?

Thanks!

Edit:
I ended up performing a new operation for each desired option. Also, I probably shouldn't have used years as an example since that was misleading.

E.g. Instead of
TABLE FILE CARS
COUNT
MODEL AS 'COUNT'
BY MANUFACTURER
ON TABLE PCHOLD FORMAT HTML
END

I used:

TABLE FILE CARS
COUNT
MODEL AS 'Ford'
WHERE MANUFACTURER EQ 'FORD'
ON TABLE PCHOLD FORMAT HTML
ON TABLE SET EMPTYREPORT ANSI
END

TABLE FILE CARS
COUNT
MODEL AS 'GM'
WHERE MANUFACTURER EQ 'GM'
ON TABLE PCHOLD FORMAT HTML
ON TABLE SET EMPTYREPORT ANSI
END

ect. for each option. It seems like there should be a better way to do this but I was able to make this work for our needs.

This message has been edited. Last edited by: Donald Dille,


Donald

WebFOCUS 8.1.04
Windows Server, Win10
Excel, PDF, HTML
 
Posts: 14 | Registered: February 13, 2015Reply With QuoteReport This Post
Virtuoso
posted Hide Post
By finding or constructing a datasource that covers all the values,
and using either JOIN or MATCH FILE to force inclusion of those rows in the result.
 
Posts: 1925 | Location: NYC | In FOCUS since 1983 | Registered: January 11, 2005Reply With QuoteReport This Post
Expert
posted Hide Post
Another method would be to first determine the minimum and maximum years that you want in the report, then use Dialogue Manager to generate OVER statements to force the missing years. This runs if you have the IBI demo file GGORDER:

-SET &ECHO='ON';

SET HOLDLIST=PRINTONLY
SET NODATA=0
-RUN

TABLE FILE GGORDER
SUM 
COMPUTE MIN_ORDER_YEAR1/YY = MIN.ORDER_DATE; NOPRINT
COMPUTE MAX_ORDER_YEAR1/YY = MAX.ORDER_DATE; NOPRINT
COMPUTE MIN_ORDER_YEAR/A4YY = MIN_ORDER_YEAR1;
COMPUTE MAX_ORDER_YEAR/A4YY = MAX_ORDER_YEAR1;
ON TABLE HOLD AS HYEARS
END
-RUN

-READ HYEARS &MIN_ORDER_YEAR.A4. &MAX_ORDER_YEAR.A4.

DEFINE FILE GGORDER
YEAR/YY = ORDER_DATE;
END
-RUN

TABLE FILE GGORDER
COUNT 
PRODUCT_DESCRIPTION AS 'COUNT'
-*BY YEAR NOPRINT
FOR YEAR
'&MIN_ORDER_YEAR'

-SET &YEAR_REP = &MIN_ORDER_YEAR + 1;

-REPEAT END_REPEAT FOR &YEAR_REP FROM &YEAR_REP TO &MAX_ORDER_YEAR

OVER '&YEAR_REP'

-END_REPEAT

ON TABLE PCHOLD FORMAT HTML
END
-RUN


Francis


Give me code, or give me retirement. In FOCUS since 1991

Production: WF 7.7.05M, Dev Studio, BID, MRE, WebSphere, DB2 / Test: WF 8.1.05M, App Studio, BI Portal, Report Caster, jQuery, HighCharts, Apache Tomcat, MS SQL Server
 
Posts: 10577 | Location: Toronto, Ontario, Canada | Registered: April 27, 2005Reply With QuoteReport This Post
Member
posted Hide Post
Thanks, j.gross.

Wouldn't that add a number to each count? Is there a way to start the count at -1 or subtract from the total?

Also, thank you Francis but year was only an example. The options I'm working with are not always logical.

Thank you,


Donald

WebFOCUS 8.1.04
Windows Server, Win10
Excel, PDF, HTML
 
Posts: 14 | Registered: February 13, 2015Reply With QuoteReport This Post
Guru
posted Hide Post
You may find the MacGyver Technique productive in this instance.

HTH


jimster06
DevStu WF 7.6.11
W7
HTML, PDF, EXL2K
 
Posts: 252 | Location: USA | Registered: April 15, 2003Reply With QuoteReport This Post
Master
posted Hide Post
Working sample.

-* Get all CARs
TABLE FILE CAR
	BY	CAR
ON TABLE HOLD AS H_COUNTRY
END

-* Get all sales ( excluded JAPAN to simulate missing records )
TABLE FILE CAR
	SUM CNT.SALES
	BY	CAR
WHERE COUNTRY NE 'JAPAN';
ON TABLE HOLD AS H_SALES
END

-* match it
MATCH FILE H_COUNTRY
	BY	CAR
RUN
FILE H_SALES
	BY	SALES
	BY	CAR
AFTER MATCH HOLD AS H_TOTAL OLD
END

-* display
TABLE FILE H_TOTAL
	SUM	SALES
	BY	CAR
END


You could choose to disable/enable HOLDMISS ( SET HOLDMISS = ON ) if you want MISSING as output instead of 0 for missing values.

G'luck,
Dave


_____________________
WF: 8.0.0.9 > going 8.2.0.5
 
Posts: 669 | Location: Veghel, The Netherlands | Registered: February 16, 2010Reply With QuoteReport This Post
Virtuoso
posted Hide Post
MacGuyver to the rescue:
  
-* File donald01.fex
JOIN BLANK WITH MODEL IN CAR TO BLANK IN FSEQ AS M_
DEFINE FILE CAR
BLANK/A1 WITH MODEL=' ';
YEAR/I4=DECODE COUNTRY(ENGLAND 2008 FRANCE 2010 ITALY 2013 JAPAN 2015 ELSE 2005);
YEARF/I4=2004 + ORDER;
END
TABLE FILE CAR
SUM 
FST.YEAR FST.YEARF
COMPUTE CNTMODEL/I5=IF FST.YEAR EQ FST.YEARF THEN CNT.MODEL ELSE 0;
BY YEARF
BY YEAR
WHERE YEARF LE YEAR;
ON TABLE HOLD
END
TABLE FILE HOLD
SUM CNTMODEL
BY YEARF
END


The MacGuyver master:
  
 FILE=FSEQ, SUFFIX=FIX, DATASET=FOCALPOINT/FSEQ.TXT, $
  SEGNAME=SEG1
   FIELD=BLANK, CONTROL, A1, A1, $
  SEGNAME=SEG2, PARENT=SEG1, OCCURS=VARIABLE
   FIELD=WHATEVER, , A1, A1, $
   FIELD=COUNTER, ORDER, I4,  I4,$


The MacGuyver data file:
  
 FSEQFILEFORMCGUYVERFSEQFILEFORMCGUYVERFSEQFILEFORMCGUYVERFSEQFILEFORMCGUYVERFSEQFILEFORMCGUYVERFSEQFILEFORMCGUYVER

Note: the first character must be a blank!


Daniel
In Focus since 1982
wf 8.202M/Win10/IIS/SSA - WrapApp Front End for WF

 
Posts: 1976 | Location: Tel Aviv, Israel | Registered: March 23, 2006Reply With QuoteReport This Post
  Powered by Social Strata  
 

Focal Point    Focal Point Forums  Hop To Forum Categories  WebFOCUS/FOCUS Forum on Focal Point     [SOLVED] Display all possible values on COUNT

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