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
August 02, 2011, 06:10 PM
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
Waz...
Prod:
WebFOCUS 7.6.10/8.1.04
Upgrade:
WebFOCUS 8.2.07
OS:
Linux
Outputs:
HTML, PDF, Excel, PPT
In Focus since 1984
Pity the lost knowledge of an old programmer!
August 03, 2011, 08:45 AM
George Patton
You could try something like defining:
ROWNUMBER/I2=ROWNUMBER + 1;
and then use BY ROWNUMBER NOPRINT as your first sort field.
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
August 03, 2011, 09:55 AM
GamP
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
August 03, 2011, 10:04 AM
j.gross
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
August 03, 2011, 10:17 AM
ColdWhiteMilk
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
June 27, 2016, 10:57 AM
cs_source
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
June 28, 2016, 05:35 AM
Ruchika Gusain
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.