Focal Point Banner
Community Center Education Summit Technical Support User Groups
Let's Get Social!

Facebook Twitter LinkedIn YouTube
Focal Point    Focal Point Forums  Hop To Forum Categories  WebFOCUS/FOCUS Forum on Focal Point     [CLOSED] Masking Numbers in a Table
Go
New
Search
Notify
Tools
Reply
  
[CLOSED] Masking Numbers in a Table
 Login/Join
 
Platinum Member
posted
FocalPoint Community,

Here is my challenge:

Currently my procedure masks values less than 5, and it works great in my printed table. However, the point of masking the cells is so no one who accesses our public-facing portal can deduce the masked value. Therefore, I need a secondary suppression. Here is how the data looks right now:



Joe's Cars	4,216	3,830	3,500	3,348	3,332	3,315	
Ed's Car Deals	260	258	282	274	284	299	
Auto Hut	342	279	230	190	197	204	
Charlie's        ^	^	^	^	^	7	
Autos4U 	6	^	^	^	5	^	


Total	       4,830	4,532	4,204	4,029	3,822	3,830	  


If someone looks at the data for the first, fifth or sixth columns, all the person would have to do is subtract the other numbers from the total and the masked value is known. I need a way to place a secondary suppression on the columns.

Does anyone have any ideas as to the best way to accomplish this objective?

Basically, in this example I need to mask the 6 in the first column, the five in fifth column and the 7 in the sixth column.

So, I'd like a way to look at the next minimum number in the column and mask it but only when there is only one data point that is masked. The second through fourth columns work fine with the current suppression technique as listed below in the code like this:

  DEFINE FILE EXTDATA
SALES/D20 MISSING ON NEEDS ALL DATA = IF SALES LE 15 THEN MISSING ELSE SALES;
END


Below is my code. I'd appreciate any suggestions.

 
DEFINE FILE CAR
SALESSET/I4 = EDIT(SALES_YEAR);
YR /YY = SALESSET;

END

-SET &MAXYR = &YR;
-SET &MINYR = &MAXYR-5;

TABLE FILE CAR
-* SET values for small protect processing and -INCLUDE.
-* Some choices are optional.
-*INCLUDE small_cell_protect_5
-*-----------------------------------------------------------------------------------------------
SUM
SUM.CAR.SALES
BY  CAR.COUNTRY
BY  CAR.DEALERSHIP
BY  CAR.SALES_YEAR
BY  CAR.YR
BY  LOWEST CAR.CAR.SALES
WHERE ( CAR.SALES_QTR EQ 'First' )
WHERE CAR.DEALERSHIP EQ &DEALERSHIP.(FIND CAR.DEALERSHIP IN CAR|FORMAT=A66V).DEALERSHIP:.QUOTEDSTRING;
WHERE (CAR.YR GE &MINYR);
WHERE (CAR.YR LE &MAXYR);
ON TABLE HOLD AS EXTDATA FORMAT FOCUS
-*ON TABLE PCHOLD FORMAT HTML
END
-RUN

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

DEFINE FILE EXTDATA
SALES/D20 MISSING ON NEEDS ALL DATA = IF SALES LE 15 THEN MISSING ELSE SALES;
END

-*Below is where I need to figure out how to do a secondary suppression if the value is less than 10

-* CALCULATE INCREASE PCT FOR DETAILED ROWS
TABLE FILE EXTDATA
SUM
SALES/D20
	COMPUTE LAG_A1/P9C MISSING ON = IF SALES EQ LAST SALES THEN LAST SALES ELSE MISSING;
	COMPUTE LAG_A2/P9C MISSING ON = IF SALES EQ LAST SALES THEN LAST LAG_A1 ELSE MISSING;
	COMPUTE LAG_A3/P9C MISSING ON = IF SALES EQ LAST SALES THEN LAST LAG_A2 ELSE MISSING;
	COMPUTE LAG_A4/P9C MISSING ON = IF SALES EQ LAST SALES THEN LAST LAG_A3 ELSE MISSING;
	COMPUTE LAG_A5/P9C MISSING ON = IF SALES EQ LAST SALES THEN LAST LAG_A4 ELSE MISSING;
	COMPUTE ONEPCTCHG/P6% MISSING  ON = IF LAG_A1 GT 0 AND SALES GT 0 THEN (SALES - LAG_A1)/ LAG_A1 * 100 ELSE MISSING;
	COMPUTE FIVEPCTCHG/P6% MISSING ON = IF LAG_A5 GT 0 AND SALES GT 0 THEN (SALES - LAG_A5)/ LAG_A5 * 100 ELSE MISSING;
BY COUNTRY
BY DEALERSHIP
BY  TOTAL COMPUTE ROWID/I2 = 1;
BY  SALES/A46V
BY 	YR
ON  TABLE HOLD AS DETDATA FORMAT FOCUS
-*ON  TABLE PCHOLD FORMAT HTML
END
-RUN
END

-* CALCULATE INCREASE PCT FOR DETAILED ROWS
TABLE FILE EXTDATA
SUM
SALES/D20
	COMPUTE LAG_A1/P9C MISSING ON = IF SALES EQ LAST SALES THEN LAST SALES ELSE MISSING;
	COMPUTE LAG_A2/P9C MISSING ON = IF SALES EQ LAST SALES THEN LAST LAG_A1 ELSE MISSING;
	COMPUTE LAG_A3/P9C MISSING ON = IF SALES EQ LAST SALES THEN LAST LAG_A2 ELSE MISSING;
	COMPUTE LAG_A4/P9C MISSING ON = IF SALES EQ LAST SALES THEN LAST LAG_A3 ELSE MISSING;
	COMPUTE LAG_A5/P9C MISSING ON = IF SALES EQ LAST SALES THEN LAST LAG_A4 ELSE MISSING;
	COMPUTE ONEPCTCHG/P6% MISSING  ON = IF LAG_A1 GT 0 AND SALES GT 0 THEN (SALES - LAG_A1)/ LAG_A1 * 100 ELSE MISSING;
	COMPUTE FIVEPCTCHG/P6% MISSING ON = IF LAG_A5 GT 0 AND SALES GT 0 THEN (SALES - LAG_A5)/ LAG_A5 * 100 ELSE MISSING;
BY COUNTRY
BY DEALERSHIP
BY  TOTAL COMPUTE ROWID/I2 = 5;
BY  TOTAL COMPUTE SORT/A66V = 'Total';
BY  TOTAL COMPUTE SALES/A46V = 'Total';
BY 	YR
ON  TABLE HOLD AS TOTDATA FORMAT FOCUS
-*ON  TABLE PCHOLD FORMAT HTML
END
-RUN
END

TABLE FILE DETDATA
PRINT
	SALES
	LAG_A1
	LAG_A2
	LAG_A3
	LAG_A4
	LAG_A5
	ONEPCTCHG
	FIVEPCTCHG
BY COUNTRY
BY DEALERSHIP
BY	ROWID
BY SORT
BY SALES
BY	YR
ON TABLE HOLD AS RPTDATA FORMAT FOCUS
-*ON TABLE PCHOLD FORMAT HTML
MORE
FILE TOTDATA
END
-RUN

-GOTO LBL_PRINT;

-******PRINT SECTION BEGINS HERE ******
-LBL_PRINT

-DEFAULTH &YR1 = 0, &YR2 = 0, &YR3 = 0, &YR4 = 0, &YR5 = 0, &YR6 = 0;
DEFINE FILE RPTDATA
COL1/D12C MISSING ON = IF YR EQ &YR1.EVAL THEN SALES ELSE MISSING;
COL2/D12C MISSING ON = IF YR EQ &YR2.EVAL THEN SALES ELSE MISSING;
COL3/D12C MISSING ON = IF YR EQ &YR3.EVAL THEN SALES ELSE MISSING;
COL4/D12C MISSING ON = IF YR EQ &YR4.EVAL THEN SALES ELSE MISSING;
COL5/D12C MISSING ON = IF YR EQ &YR5.EVAL THEN SALES ELSE MISSING;
COL6/D12C MISSING ON = IF YR EQ &YR6.EVAL THEN SALES ELSE MISSING;
COL7/P6C% MISSING ON = IF YR EQ &YR6.EVAL THEN ONEPCTCHG ELSE MISSING;
COL8/P6C% MISSING ON = IF YR EQ &YR6.EVAL THEN FIVEPCTCHG ELSE MISSING;
END

TABLE FILE RPTDATA
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'
	COL7 AS '1 Year Chg'
	COL8 AS '5 Year Chg'
BY COUNTRY NOPRINT
BY DEALERSHIP AS 'Dealer'
BY  ROWID   NOPRINT
BY  SALES/A46V AS 'SALES'
ON TABLE SUBHEAD
"Sales Summary"
"Years &MINYR - &MAXYR"
"&DEALERSHIP"
FOOTING
" Source Notes"
ON TABLE SET PAGE-NUM NOLEAD
ON TABLE NOTOTAL
ON TABLE PCHOLD FORMAT HTML
ON TABLE SET HTMLCSS ON
ON TABLE SET STYLE *
ENDSTYLE
END
-RUN
-LBL_END;

 

This message has been edited. Last edited by: FP Mod Chuck,


WebFocus 8.1.05M
AppStudio 8.1.05M

 
Posts: 119 | Registered: September 18, 2015Reply With QuoteReport This Post
Platinum Member
posted Hide Post
I need to update my profile which I'll do here in a bit, but I forgot to mention I'm working in 8.2


WebFocus 8.1.05M
AppStudio 8.1.05M

 
Posts: 119 | Registered: September 18, 2015Reply With QuoteReport This Post
Platinum Member
posted Hide Post
Everyone,
Over the weekend, I came up with another way to explain this using Excel terminology.

In Excel you can use a combination of min and small to find the minimum and then the next smallest value.

What I'm trying to accomplish is similar. I'm trying to mask cells less or equal to 4. If there is only one such cell in a column, I want to mask the next smallest number.


WebFocus 8.1.05M
AppStudio 8.1.05M

 
Posts: 119 | Registered: September 18, 2015Reply With QuoteReport This Post
Virtuoso
posted Hide Post
Julie

If this is still an issue you should probably open a case with techsupport..


Thank you for using Focal Point!

Chuck Wolff - Focal Point Moderator
WebFOCUS 7x and 8x, Windows, Linux All output Formats
 
Posts: 1506 | Location: Customer Support | Registered: April 12, 2005Reply With QuoteReport This Post
  Powered by Social Strata  
 

Focal Point    Focal Point Forums  Hop To Forum Categories  WebFOCUS/FOCUS Forum on Focal Point     [CLOSED] Masking Numbers in a Table

Copyright © 1996-2018 Information Builders, leaders in enterprise business intelligence.