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>,

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

You could try something like defining:

ROWNUMBER/I2=ROWNUMBER + 1;

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

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?

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

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.

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.

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

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