[CLOSED]Data Masking Issue with Numeric Format

Challenge: To replace a negative integer with a caret in a numeric field

Here is my procedure code and my small cell masking code. We're trying to replace the value of -1 with a caret in the procedure so that when the resulting output is exported to Excel, it shows as a caret with the rest of the values appearing in numeric format for calculations. Below is my code followed by some sample output.


-* EXTRACT SOURCE DATA
DEFINE FILE MV_MODEL_CAR
TERMSET/I4 = EDIT(CALENDAR_YEAR);
YR /YY = TERMSET;
SORT_MODEL/A46 = IF DEALER_NAME EQ 'BMW'  THEN '    BMW' ELSE
	         IF DEALER_NAME EQ 'Honda' THEN '   Honda' ELSE
	         IF DEALER_NAME EQ 'Volvo'   THEN '  Volvo'    ELSE CAR_NAME ;
END

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

TABLE FILE MV_MODEL_CAR
-* SET values for small protect processing and -INCLUDE.
-* Some choices are optional.
-*INCLUDE small_cell_protect_5
-*-----------------------------------------------------------------------------------------------
SUM
SUM.MV_MODEL_CAR.MV_MODEL_CAR.COUNT AS 'COUNTS'
BY  LOWEST MV_MODEL_CAR.MV_MODEL_CAR.DELEAR_NAME
BY  MV_MODEL_CAR.MV_MODEL_CAR.CALENDAR_YEAR
BY  MV_MODEL_CAR.MV_MODEL_CAR.YR
BY  LOWEST MV_MODEL_CAR.MV_MODEL_CAR.SORT_MODEL
WHERE (MV_MODEL_CAR.MV_MODEL_CAR.YR GE &MINYR);
WHERE (MV_MODEL_CAR.MV_MODEL_CAR.YR LE &MAXYR);
ON TABLE HOLD AS EXTDATA FORMAT FOCUS
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
CALENDAR_YEAR/A100 =EDIT(YR)
END

TABLE FILE EXTDATA
SUM
COUNTS
BY DEALER_NAME
BY CALENDAR_YEAR
BY LOWEST SORT_MODEL
ON TABLE SET ASNAMES ON
ON TABLE NOTOTAL
ON TABLE HOLD AS REPINPUT
ON TABLE SET STYLE *
ENDSTYLE
END

-* SET values for small protect processing and -INCLUDE.
-* Some choices are optional.
-SET &input   = 'repinput';
-SET &output  = 'finaldata';
-SET &dim1    = 'DEALER_NAME';
-SET &dim1_id = 'SORT_MODEL';
-SET &dim2    = 'CALENDAR_YEAR';
-SET &measure = 'COUNTS';
-INCLUDE IBFS:/small_protect.fex

-* Create report based on small protect output.

TABLE FILE finaldata

ON TABLE SUBHEAD
"Years &MINYR - &MAXYR"
"&DEALER_NAME"
ON TABLE SUBFOOT
"- Cells have been marked to indicate no data."

 SUM &measure
	 BY &dim1._SORT NOPRINT
	 BY &dim1      AS 'DEALER_NAME'
	 BY LOWEST &dim1_id   AS 'MODEL'
-* ACROSS &dim2._SORT NOPRINT
 ACROSS &dim2 AS 'Year'
 WHERE &dim2 NE 'Total'
 ON &dim1  SUBFOOT
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 *
     INCLUDE = /WFC/Car_style.sty,

ENDSTYLE
END
-RUN
-LBL_END;




-*--------------------------------THIS IS THE SMALL CELL PROTECT FILE --------------------------------------------------------------

-*-----------------------------------------------------------------------------------------------
-* Phase 0 - Initialize
-* Establish default values. This defaulting sytax requires calling proceudre to -SET
-* to override the default parameters.
-DEFAULTH &small   = '5';
-DEFAULTH &tsmall  = '3';
-DEFAULT &input   = 'input';
-DEFAULT &output  = 'output';
-DEFAULT &dim1    = ' ';
-DEFAULT &dim1_id = '&dim1.EVAL';
-DEFAULT &dim2    = ' ';
-DEFAULTH &dim2_id = '&dim2.EVAL';
-DEFAULT &measure = ' ';

 SET ASNAME=ON,HOLDLIST=PRINTONLY
 FILEDEF small_cell_data DISK small_cell_data.ftm (APPEND

 DEFINE FILE &input.EVAL
 DIM1_ID/A100 = &dim1_id.EVAL;
 DIM1/A100    = &dim1.EVAL;
 DIM2_ID/A100 = &dim2_id.EVAL;
 DIM2/A100    = &dim2.EVAL;
 MEASURE/D20  = &measure.EVAL;
 END

-*-----------------------------------------------------------------------------------------------
-* Phase 1 - Dim Orders
-* Assign a number to each distinct dimension value and append a Total entry with the last value.
-* This is used for sorting the rows and columns in the final report, including the totals which
-* are calculated in the reporting data (not doing ROW-TOTAL and COLUMN-TOTAL features).
 TABLE FILE &input.EVAL
 SUM     DIM1 NOPRINT
 COMPUTE DIM1_SORT/I11 = IF LAST DIM1 EQ DIM1 THEN DIM1_SORT ELSE DIM1_SORT + 1;
      BY DIM1
 ON TABLE HOLD AS dim1 FORMAT FOCUS INDEX DIM1
 END
 SQL INSERT INTO dim1 VALUES ( 'Total' , 999999999 );
 END

 TABLE FILE &input.EVAL
 SUM     DIM2 NOPRINT
 COMPUTE DIM2_SORT/I11 = IF LAST DIM2 EQ DIM2 THEN DIM2_SORT ELSE DIM2_SORT + 1;
      BY DIM2
 ON TABLE HOLD AS dim2 FORMAT FOCUS INDEX DIM2
 END
 SQL INSERT INTO dim2 VALUES ( 'Total' , 999999999 );
 END

-*-----------------------------------------------------------------------------------------------
-* Phase 2 - Main Data
-* Define a counter SMALL_COUNTER for small values. Define NONSMALL_TOTAL for summing all non-small values.
 DEFINE FILE &input.EVAL ADD
 SMALL_COUNTER/I11  = IF MEASURE LT &small THEN 1       ELSE 0;
 NONSMALL_TOTAL/I11 = IF MEASURE GE &small THEN MEASURE ELSE 0;
 END

 TABLE FILE &input.EVAL
 PRINT   MEASURE
         SMALL_COUNTER
         NONSMALL_TOTAL
 COMPUTE DETAIL/A1 = 'D';
      BY DIM1_ID
      BY DIM1
      BY DIM2_ID
      BY DIM2
 ON TABLE HOLD AS small_cell_data FORMAT ALPHA
 END

-*-----------------------------------------------------------------------------------------------
-* Phase 3 - Row/Col Totals
-* Calculate totals for both dimensions (what will become totals row and column in the final
-* report) and append to previous hold file.
 TABLE FILE &input.EVAL
 SUM     DIM1_ID
         DIM1
 COMPUTE DIM2_ID/A100 = ' ';
 COMPUTE DIM2/A100   = 'Total';
         MEASURE
		 SMALL_COUNTER
         NONSMALL_TOTAL
 COMPUTE DETAIL/A1 = 'T';
      BY DIM1    NOPRINT
      BY DIM1_ID NOPRINT
 ON TABLE HOLD AS small_cell_data FORMAT ALPHA
 END

 TABLE FILE &input.EVAL
 SUM
 COMPUTE DIM1_ID/A100 = ' ';
 COMPUTE DIM1/A100   = 'Total';
         DIM2_ID
         DIM2
         MEASURE
		 SMALL_COUNTER
         NONSMALL_TOTAL
 COMPUTE DETAIL/A1 = 'T';
      BY DIM2    NOPRINT
      BY DIM2_ID NOPRINT
 ON TABLE HOLD AS small_cell_data FORMAT ALPHA
 END

-*-----------------------------------------------------------------------------------------------
-* Phase 4 - Join Sorts
-* Join to dim sort tables to get proper column and row sorting, including Total row and column
-* at the bottom and right.
 JOIN DIM1 IN small_cell_data TO DIM1 IN dim1 AS J1
 JOIN DIM2 IN small_cell_data TO DIM2 IN dim2 AS J2

-*-----------------------------------------------------------------------------------------------
-* Phase 5 - Final Logic
-* Join to dim sort tables to get proper column and row sorting, including Total row and column
-* at the bottom and right.
-SET &ECHO='ALL';
 TABLE FILE small_cell_data
 PRINT
 DIM1           -*AS '&dim1'
 DIM1_ID        -*AS '&dim1_id'
 DIM1_SORT      -*AS '&dim1._SORT'
 DIM2           -*AS '&dim2'
 DIM2_ID        -*AS '&dim2_id'
 DIM2_SORT      -*AS '&dim2._SORT'
 DETAIL
 MEASURE
 SMALL_COUNTER
 NONSMALL_TOTAL
 COMPUTE MEASURE_STR/I11 =
         IF DETAIL EQ 'T' AND SMALL_COUNTER GE 2 AND MEASURE-NONSMALL_TOTAL LE &tsmall THEN NONSMALL_TOTAL ELSE
         IF DETAIL EQ 'T' AND SMALL_COUNTER EQ 1                                       THEN NONSMALL_TOTAL ELSE
		                                                                                    MEASURE;
 COMPUTE MEASURE_END/I11 =
         IF DETAIL EQ 'T' AND SMALL_COUNTER GE 2 AND MEASURE-NONSMALL_TOTAL LE &tsmall THEN MEASURE_STR + &small - 1 ELSE
         IF DETAIL EQ 'T' AND SMALL_COUNTER EQ 1                                       THEN MEASURE_STR + &small - 1 ELSE
                                                                                            0;
-*updated I11 to d11 to shift from integer to decimal in order to display comma formatting
COMPUTE FMEASURE/I11 = IF DETAIL EQ 'D' AND MEASURE LT &small THEN -1
					    ELSE IF MEASURE_END EQ 0 THEN MEASURE_STR;
-* COMPUTE FMEASURE/A100 =
-*         IF DETAIL EQ 'D' AND MEASURE LT &small THEN  '^'                                                                            ELSE
-*         IF MEASURE_END EQ 0                    THEN FPRINT(MEASURE_STR, 'D11', 'A40');-*                                               ELSE
-*		                                             FPRINT(MEASURE_STR, 'D11', 'A40') || ' -' || FPRINT(MEASURE_END, 'D11', 'A40');
               -* AS '&measure'
 ON TABLE HOLD AS small_cell_data FORMAT ALPHA
 END


 TABLE FILE small_cell_data
 PRINT
 DIM1           AS '&dim1'
 DIM1_ID        AS '&dim1_id'
 DIM1_SORT      AS '&dim1._SORT'
 DIM2           AS '&dim2'
 DIM2_ID        AS '&dim2_id'
 DIM2_SORT      AS '&dim2._SORT'
 DETAIL
 MEASURE
 SMALL_COUNTER
 NONSMALL_TOTAL
 MEASURE_STR
 MEASURE_END
 FMEASURE      AS '&measure'
 ON TABLE HOLD AS &output
 END
-RUN

Here is some sample output:

CAR Inventory							
Inventory by Model							
Sales Year 2012-2017							
							
							
		Year					
		2012	2013	2014	2015	2016	2017
Dealership	Model						
Abdul's Cars	Honda	25	61	274	94	159	352
	Ford	50	76	76	63	102	121
	Toyota	73	92	96	76	68	84
	Subaru	13	6	5	8	9	11
	Volvo	20	21	19	14	24	28
	Jaguar	6	8	8	-1	-1	-1
	BMW	-	-	-	-	-1	-1
Total		187	264	478	254	360	594

This message has been edited. Last edited by: JulieA,

WebFocus 8.2.04
WebFocus 8.2.04

Posts: 191 | Registered: September 18, 2015

MartinY

Virtuoso

posted

Hide Post

What about something such as this

SET NODATA = '-'
TABLE FILE CAR
SUM COMPUTE NET_SALES /I6 MISSING ON = IF SALES LE 0 THEN MISSING ELSE IF (SALES - RETAIL_COST) LE -1 THEN MISSING ELSE SALES - RETAIL_COST;
BY CAR
BY MODEL
ON TABLE PCHOLD FORMAT XLSX
ON TABLE SET BYDISPLAY ON
END

The trick is to assign a null value (MISSING) when the field reach the condition where you want to put the caret and have the NODATA set to a caret '-'

WF versions : Prod 8.2.04M gen 33, Dev 8.2.04M gen 33, OS : Windows, DB : MSSQL, Outputs : HTML, Excel, PDF
In Focus since 2007

Posts: 2409 | Location: Montreal Area, Qc, CA | Registered: September 25, 2013

Ignored post by MartinY posted

Show Post

JulieA

Platinum Member

posted

Hide Post

As always, thank you Martin. I realize I did not elaborate on an important piece pertinent to this particular situation.

The SET NODATA is set in the admin console as a dash (-). That covers situations of no data reported for specific data elements. So, if Joe's Cars has no Toyotas sold for 2012, it would be indicated by a dash.

The caret is used for small-cell situations. For example, if the number of Toyota vehicles sold by Joe's Cars for 2013 is less than 10, we would use a caret.

Additionally, we have a second level to the protection/masking procedure that results in ranges.

If I override the AdminConsole and SET NODATA to a caret within the fex, WebFocus changes the dashes to carets in addition to using the caret for the masked cells. The masking works fine, but it's left in text. I need to convert the results to numeric format for users when downloading to Excel.

That last section of code refers to my small-cell protection procedure.

WebFocus 8.2.04
WebFocus 8.2.04

Posts: 191 | Registered: September 18, 2015

Ignored post by JulieA posted

Show Post

Doug

Expert

posted

Hide Post

Can you reproduce that scenario using the CAR file with MPG or SEATS of LENGTH? Something that we can work with?

Thanks, Doug

Posts: 3132 | Location: Tennessee, Nashville area | Registered: February 23, 2005

Ignored post by Doug posted

Show Post

Tony A

Expert

posted

Hide Post

Hi Julie,

Would I be correct in thinking that your issue is that you want different values output depending upon whether the output formt (&output in your case) is HTML, EXL2K, XLSX etc.?

If so then you will probably know that you should change the MEASURE that you need for the output format chosen. Also I am sure you know that there are a few ways of doing this (as always!) but generally they involve judicious use of DM code.

-SET &Dummy = '&WFFMT.(HTML,XLSX,PDF).Output format';

-SET &Suffix = IF &WFFMT NE 'XLSX' THEN '_A' ELSE '';

TABLE FILE CAR
SUM DCOST
    COMPUTE MEASURE/I11 = IF DCOST LE 5000 THEN -1 ELSE DCOST;
    COMPUTE MEASURE_A/A40 = IF MEASURE EQ -1 THEN '^' ELSE RJUST(20,FPRINT(DCOST,'D11','A20'),'A20');
BY COUNTRY
BY CAR
BY MODEL
ON TABLE HOLD AS TEMPHLD1
END
-RUN

TABLE FILE TEMPHLD1
SUM MEASURE&Suffix.EVAL
BY COUNTRY
BY CAR
BY MODEL
ON TABLE PCHOLD FORMAT &WFFMT
END
-RUN

If this is not what you mean then you might need to be more explicit?

T

In FOCUS since 1986	WebFOCUS Server 8.2.01M, thru 8.2.07 on Windows Svr 2008 R2
	WebFOCUS App Studio 8.2.06 standalone on Windows 10

Posts: 5694 | Location: United Kingdom | Registered: April 08, 2004

Ignored post by Tony A posted

Show Post

JulieA

Platinum Member

posted

Hide Post

TonyA:

Let me try again to explain things a different way.

We need to convert the value from text to numeric no matter the format (i.e. HTML, Excel).

And to recap from above, we also need to be able to use carets for masked cells and dashes for instances where data is not reported.

Doug:
We might be able to reproduce using the CAR file. We'll see what we can do today.

Thank you both.

WebFocus 8.2.04
WebFocus 8.2.04

Posts: 191 | Registered: September 18, 2015

Ignored post by JulieA posted

Show Post

MartinY

Virtuoso

posted

Hide Post

quote:

We need to convert the value from text to numeric no matter the format (i.e. HTML, Excel).

This is where is the issue : a caret and a dash are NOT numeric value.
And this is where the SET NODATA is for : have a non-numeric value into a numeric field. But you cannot have two different value assigned (caret and dash).

To have two different "mask", you need it as alpha but they won't be numeric field in Excel

SET NODATA = '-'
TABLE FILE CAR
SUM COMPUTE NET_SALES /A10 MISSING ON = IF SALES LE 0 THEN MISSING ELSE IF (SALES - RETAIL_COST) LE -1 THEN '^' ELSE FPRINT((SALES - RETAIL_COST), 'I6', 'A10');
BY CAR
BY MODEL
ON TABLE PCHOLD FORMAT XLSX
ON TABLE SET BYDISPLAY ON
ON TABLE SET STYLE *
     INCLUDE = IBFS:/EDA/EDASERVE/_EDAHOME/ETC/warm.sty,
$
TYPE=DATA,
     COLUMN=NET_SALES,
     JUSTIFY=RIGHT,
$
ENDSTYLE
END
-RUN

So you may need to make choice here. It seems that you cannot have all : two different mask and keep it as numeric.
Or maybe another IB feature such as FML may solve your issue, but I'm not familiar with FML and I doubt that it will.

WF versions : Prod 8.2.04M gen 33, Dev 8.2.04M gen 33, OS : Windows, DB : MSSQL, Outputs : HTML, Excel, PDF
In Focus since 2007

Posts: 2409 | Location: Montreal Area, Qc, CA | Registered: September 25, 2013

Ignored post by MartinY posted

Show Post

Tony A

Expert

posted

Hide Post

quote:
We need to convert the value from text to numeric no matter the format

Julie,

Then, unless you want to intercept the output format and modify the value(s) prior to delivery of output, you are needing this for the application in which you are opening the output e.g. MS Excel. That is where you need to apply your logic then, by using (for instance) MS Excel templates with Macros.

T

In FOCUS since 1986	WebFOCUS Server 8.2.01M, thru 8.2.07 on Windows Svr 2008 R2
	WebFOCUS App Studio 8.2.06 standalone on Windows 10

Posts: 5694 | Location: United Kingdom | Registered: April 08, 2004

Ignored post by Tony A posted

Show Post

JulieA

Platinum Member

posted

Hide Post

Everyone:

Thanks for all the suggestions. We've decided to go a different way with this, and I'm going to open a new discussion within the next few minutes.

WebFocus 8.2.04
WebFocus 8.2.04

Posts: 191 | Registered: September 18, 2015

Ignored post by JulieA posted

Show Post

Please Wait. Your request is being processed...

Read-Only Topic

Focal Point

Focal Point Forums

WebFOCUS/FOCUS Forum on Focal Point

[CLOSED]Data Masking Issue with Numeric Format

Focal Point | Privacy Statement | Terms of Service