I'd like to apply a SET NODATA character to two specific columns-those of the 1 Yr Pct Chg and the 5 Yr Pct Chg in the final output.
Is there a way to do that other than writing something like: IF THIS THEN THIS ELSE IF THIS THEN '-'?
Please see COL7 and COL8 below. I've looked at a lot of manuals this morning, and I know I'm overlooking something.
I asked because I tried it.
DEFINE FILE GROCERY_SALES
YR/I4=EDIT(SALES_YEAR);
SORT_STORE/A66V = STORE_NAME;
END
-SET &MAXYR = &YR;
-SET &MINYR = &MAXYR - 5;
TABLE FILE GROCERY_SALES
SUM
GROCERT_SALES.GROCERT_SALES.UNITS_SOLD AS 'SC_COUNTS'
BY GROCERY_SALES.GROCERY_SALES.REGION_NO
BY GROCERY_SALES.GROCERY_SALES.REGION_NAME
BY GROCERY_SALES.GROCERY_SALES.STORE_NAME
BY GROCERY_SALES.GROCERY_SALES.YR
BY SORT_STORE/A66V
WHERE (GROCERY_SALES.GROCERY_SALES.YR GE &MINYR);
WHERE (GROCERY_SALES.GROCERY_SALES.YR LE &MAXYR);
WHERE GROCERY_SALES.GROCERY_SALES.SALES_QUARTER EQ &SALES_QUARTER.(FIND GROCERY_SALES.GROCERY_SALES.SALES_QUARTER IN GROCERY_SALES|FORMAT=A13V).SALES_QUARTER:.QUOTEDSTRING;
ON TABLE HOLD AS EXTDATA
END
-RUN
-GOTO LBL_PRINT;
*There are two other tables users can select in a dropdown menu. I chose not to include them here. The code is the same.
-LBL_PRINT
-* EXTRACT MAXIMUM YEAR IN A VARIABLE
-DEFAULTH &YRMAX = 0
TABLE FILE EXTDATA
SUM MAX.YR/I4 AS 'YRMAX'
ON TABLE HOLD AS MAXYR FORMAT BINARY
END
-RUN
-READFILE MAXYR
-SET &YRMAX = &YRMAX;
-TYPE &YRMAX
-* EXTRACT ALL INDIVIDUAL YEARS IN A VARIABLE
-DEFAULTH &EXTYR = 0
TABLE FILE EXTDATA
BY YR/I4 AS 'EXTYR'
ON TABLE HOLD AS YRLIST FORMAT BINARY
END
-RUN
-SET &NBYR = &LINES;
-REPEAT READYR FOR &I FROM 1 TO &NBYR STEP 1
-DEFAULTH &YR.&I = 0
-READFILE YRLIST
-SET &YR.&I = &EXTYR;
-TYPE &YR.&I
-READYR
TABLE FILE EXTDATA
SUM
SC_COUNTS
COMPUTE LAG_A1 MISSING ON = IF SORT_INST EQ LAST SORT_STORE THEN LAST SC_COUNTS ELSE 0;
COMPUTE LAG_A2 MISSING ON = IF SORT_STORE EQ LAST SORT_STORE THEN LAST LAG_A1 ELSE 0;
COMPUTE LAG_A3 MISSING ON = IF SORT_STORE EQ LAST SORT_STORE THEN LAST LAG_A2 ELSE 0;
COMPUTE LAG_A4 MISSING ON = IF SORT_STORE EQ LAST SORT_STORE THEN LAST LAG_A3 ELSE 0;
COMPUTE LAG_A5 MISSING ON = IF SORT_STORE EQ LAST SORT_STORE THEN LAST LAG_A4 ELSE 0;
COMPUTE PCTCHG1/D11.1% MISSING ON = IF LAG_A1 GT 0 AND SC_COUNTS GT 0 THEN (SC_COUNTS - LAG_A1) / LAG_A1 *100 ELSE MISSING;
COMPUTE PCTCHG5/D11.1% MISSING ON = IF LAG_A5 GT 0 AND SC_COUNTS GT 0 THEN (SC_COUNTS - LAG_A5) / LAG_A5 *100 ELSE MISSING;
BY TOTAL COMPUTE ROWID/I2 = 1;
BY REGION_NO/A3V
BY REGION_NAME/A20V
BY SORT_STORE/A66V
BY YR
ON TABLE HOLD AS INSTDATA
END
-RUN
-*-EXIT
TABLE FILE EXTDATA
SUM
SC_COUNTS
COMPUTE LAG_A1 MISSING ON = IF SORT_STORE EQ LAST SORT_STORE THEN LAST SC_COUNTS ELSE 0;
COMPUTE LAG_A2 MISSING ON = IF SORT_STORE EQ LAST SORT_STORE THEN LAST LAG_A1 ELSE 0;
COMPUTE LAG_A3 MISSING ON = IF SORT_STORE EQ LAST SORT_STORE THEN LAST LAG_A2 ELSE 0;
COMPUTE LAG_A4 MISSING ON = IF SORT_STORE EQ LAST SORT_STORE THEN LAST LAG_A3 ELSE 0;
COMPUTE LAG_A5 MISSING ON = IF SORT_STORE EQ LAST SORT_STORE THEN LAST LAG_A4 ELSE 0;
COMPUTE PCTCHG1/D11.1% MISSING ON = IF LAG_A1 GT 0 AND SC_COUNTS GT 0 THEN (SC_COUNTS - LAG_A1) / LAG_A1 *100 ELSE MISSING;
COMPUTE PCTCHG5/D11.1% MISSING ON = IF LAG_A5 GT 0 AND SC_COUNTS GT 0 THEN (SC_COUNTS - LAG_A5) / LAG_A5 *100 ELSE MISSING;
BY TOTAL COMPUTE ROWID/I2 = 2;
BY REGION_NO/A3V
BY TOTAL COMPUTE REGION_NAME/A20V= 'Total';
BY TOTAL COMPUTE SORT_STORE/A66V = ' ';
BY YR
ON TABLE HOLD AS SUBDATA
END
-RUN
-*-EXIT
TABLE FILE EXTDATA
SUM
SC_COUNTS
COMPUTE LAG_A1 MISSING ON = IF SORT_STORE EQ LAST SORT_STORE THEN LAST SC_COUNTS ELSE 0;
COMPUTE LAG_A2 MISSING ON = IF SORT_STORE EQ LAST SORT_STORE THEN LAST LAG_A1 ELSE 0;
COMPUTE LAG_A3 MISSING ON = IF SORT_STORE EQ LAST SORT_STORE THEN LAST LAG_A2 ELSE 0;
COMPUTE LAG_A4 MISSING ON = IF SORT_STORE EQ LAST SORT_STORE THEN LAST LAG_A3 ELSE 0;
COMPUTE LAG_A5 MISSING ON = IF SORT_STORE EQ LAST SORT_STORE THEN LAST LAG_A4 ELSE 0;
COMPUTE PCTCHG1/D11.1% MISSING ON = IF LAG_A1 GT 0 AND SC_COUNTS GT 0 THEN (SC_COUNTS - LAG_A1) / LAG_A1 *100 ELSE MISSING;
COMPUTE PCTCHG5/D11.1% MISSING ON = IF LAG_A5 GT 0 AND SC_COUNTS GT 0 THEN (SC_COUNTS - LAG_A5) / LAG_A5 *100 ELSE MISSING;
BY TOTAL COMPUTE ROWID/I2 = 3;
BY TOTAL COMPUTE REGION_NO/A3V = '5';
BY TOTAL COMPUTE REGION_NAME/A20V='System Total';
BY TOTAL COMPUTE SORT_STORE/A66V = ' ';
BY YR
ON TABLE HOLD AS TOTDATA
END
-RUN
TABLE FILE INSTDATA
PRINT
SC_COUNTS
LAG_A1
LAG_A2
LAG_A3
LAG_A4
LAG_A5
PCTCHG1
PCTCHG5
BY ROWID
BY REGION_NO
BY REGION_NAME
BY SORT_STORE
BY YR
ON TABLE HOLD AS FINAL
MORE
FILE SUBDATA
MORE
FILE TOTDATA
END
-RUN
-DEFAULTH &YR1 = 0, &YR2 = 0, &YR3 = 0, &YR4 = 0, &YR5 = 0, &YR6 = 0;
DEFINE FILE FINAL
COL1/D12C MISSING ON = IF YR EQ &YR1.EVAL THEN SC_COUNTS ELSE MISSING;
COL2/D12C MISSING ON = IF YR EQ &YR2.EVAL THEN SC_COUNTS ELSE MISSING;
COL3/D12C MISSING ON = IF YR EQ &YR3.EVAL THEN SC_COUNTS ELSE MISSING;
COL4/D12C MISSING ON = IF YR EQ &YR4.EVAL THEN SC_COUNTS ELSE MISSING;
COL5/D12C MISSING ON = IF YR EQ &YR5.EVAL THEN SC_COUNTS ELSE MISSING;
COL6/D12C MISSING ON = IF YR EQ &YR6.EVAL THEN SC_COUNTS ELSE MISSING;
COL7/P6.1C% MISSING ON = IF YR EQ &YR6.EVAL THEN PCTCHG1 ELSE MISSING;
COL8/P6.1C% MISSING ON = IF YR EQ &YR6.EVAL THEN PCTCHG5 ELSE MISSING;
END
-SET &RPT_HEAD2 = IF &SALES_QUARTER EQ 'Sales Years' THEN 'Sales Years' ELSE &SALES_QUARTER;
TABLE FILE FINAL
SUM
COL1 AS '&YR1.EVAL'
COL2 AS '&YR2.EVAL'
COL3 AS '&YR3.EVAL'
COL4 AS '&YR4.EVAL'
COL5 AS '&YR5.EVAL'
COL6 AS '&YR6.EVAL'
PCTCHG1 AS '1 Year Chg'
PCTCHG5 AS '5 Year Chg'
BY REGION_NO NOPRINT
BY ROWID NOPRINT
BY REGION_NAME AS 'Region'
BY SORT_STORE/A66V AS 'Store'
ON REGION_NO SUBFOOT " "
ON TABLE SUBHEAD
"Grocery Stores International"
"Sales Summary by &RPT_HEAD"
"&RPT_HEAD2 &MINYR - &MAXYR"
"All Stores"
FOOTING
"Source: Sales Records"
"&RPT_FOOT"
ON TABLE SET PAGE-NUM NOLEAD
ON TABLE NOTOTAL
ON TABLE PCHOLD FORMAT &WFFMT.(<HTML Web Document (HTML),HTML>,<Excel XLSX (XLSX),XLSX>).Output type.
ON TABLE SET HTMLCSS ON
ON TABLE SET STYLE *
PE=REPORT, TITLETEXT='WebFOCUS Report', ORIENTATION=LANDSCAPE, $
TYPE=FOOTING, LINE=1, ITEM=1, OBJECT=TEXT, JUSTIFY=RIGHT, FONT='Calibri', SIZE=9, COLOR=RGB(0 0 0), STYLE=NORMAL, $
TYPE=FOOTING, JUSTIFY=RIGHT, FONT='Calibri', SIZE=9, COLOR=RGB(0 0 0), STYLE=ITALIC,
$
TYPE=TITLE,
BORDER-BOTTOM=ON,
BORDER-BOTTOM-COLOR=RGB (250 250 250),
JUSTIFY=RIGHT,
$
TYPE=TITLE,
COLUMN=N3,
NDSTYLE
END
-RUN
-LBL_END;
This message has been edited. Last edited by: FP Mod Chuck,
WebFocus 8.2.04 WebFocus 8.2.04
October 20, 2020, 03:31 PM
FP Mod Chuck
Julie
The NODATA is all or nothing, you will have to use a DEFINE or COMPUTE on those columns to get the results you want.
Thank you for using Focal Point!
Chuck Wolff - Focal Point Moderator WebFOCUS 7x and 8x, Windows, Linux All output Formats