Focal Point Banner


As of December 1, 2020, Focal Point is retired and repurposed as a reference repository. We value the wealth of knowledge that's been shared here over the years. You'll continue to have access to this treasure trove of knowledge, for search purposes only. Moving forward, myibi is our community platform to learn, share, and collaborate. We have the same Focal Point forum categories in myibi, so you can continue to have all new conversations there. If you need access to myibi, contact us at myibi@ibi.com and provide your corporate email address, company, and name.


Connect to myibi
Focal Point    Focal Point Forums  Hop To Forum Categories  WebFOCUS/FOCUS Forum on Focal Point     [CLOSED] SPECIAL MISSING CASE

Read-Only Read-Only Topic
Go
Search
Notify
Tools
[CLOSED] SPECIAL MISSING CASE
 Login/Join
 
Platinum Member
posted
Hello FocalPoint Community,

I have what I hope is a simple question.

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

 
Posts: 191 | Registered: September 18, 2015Report This Post
Virtuoso
posted Hide Post
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
 
Posts: 2128 | Location: Customer Support | Registered: April 12, 2005Report This Post
  Powered by Social Strata  

Read-Only Read-Only Topic

Focal Point    Focal Point Forums  Hop To Forum Categories  WebFOCUS/FOCUS Forum on Focal Point     [CLOSED] SPECIAL MISSING CASE

Copyright © 1996-2020 Information Builders