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,

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.

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

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,

You may find the MacGyver Technique productive in this instance.

HTH

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

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!