Focal Point    Focal Point Forums    WebFOCUS/FOCUS Forum on Focal Point     [CLOSED] Graphing without Some Values

 Go Search Notify Tools
 [CLOSED] Graphing without Some Values
Platinum Member
 posted October 18, 2019 03:45 PM
Focal Point Community,

This community has provided me a lot of great ideas through the years, and I have yet another challenge. I'm hoping someone will have an amazing idea.

The short version of my team's challenge is this:

• Convert masked values which appear in carets (^) from an alphanumeric field to zeroes, nulls, whatever the case may be.

• Convert all values to numeric format.

• Take the resulting file and graph it minus the nulls, zeroes, etc.

We are not able to manipulate the master file. We need to have all the entire data set present in order for our custom masking procedure to work properly.

By way of an example from the CAR file, here is some code:
``` TABLE FILE CAR
SUM
CAR.BODY.SALES
BY  CAR.COMP.CAR
ACROSS LOWEST CAR.BODY.BODYTYPE
WHERE CAR.COMP.CAR NE 'JENSEN' OR 'PEUGEOT'
ON TABLE SET ASNAMES ON
ON TABLE NOTOTAL
ON TABLE PCHOLD FORMAT HTML
ON TABLE SET HTMLEMBEDIMG ON
ON TABLE SET HTMLCSS ON
ON TABLE SET STYLE *
INCLUDE = IBFS:/EDA/EDASERVE/_EDAHOME/ETC/warm.sty,
\$
ENDSTYLE
END ```

If you look at the SEDAN column using the provided code, it has two carets.

For argument's sake, let's say those numbers were 7 and 4. Our masking procedure will mask both numbers because 7<4, and we mask anything <5; however, if we mask only one number, we mask the next smallest number. It means no one can do the math to figure out the masked values exactly.

They might know the combination of those two values is 11, but they don't know if the individual values are 6 and 5, 8 and 3, or 7 and 4.

Does anyone have any ideas on the most effective approach for this challenge? I really want to solve it soon.

I might note one suggestion made to me recently is to use -include and place my masked value file in my graph fex, do the conversions and then graph from there.

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

WebFocus 8.2.04
WebFocus 8.2.04

 Posts: 191 | Registered: September 18, 2015 IP
Virtuoso
 posted October 21, 2019 08:33 AM Hide Post
Are you trying to write a procedure that creates the masked values or are the values already masked? I've read your problem description several times and admittedly am having a hard time understanding what you're trying to accomplish.

WebFOCUS 8206, Unix, Windows

 Posts: 1853 | Location: New York City | Registered: December 30, 2015 IP
Platinum Member
 posted October 21, 2019 08:43 AM Hide Post

I apologize for any confusion.

We have a procedure that we use to mask our values. We're wanting to graph results once everything is masked but not include the masked values.

I can post the code in about an hour or two, but if you look at the CAR file example I posted last week, we'd never include in the graph those values marked with a caret.

Does that make sense?

WebFocus 8.2.04
WebFocus 8.2.04

 Posts: 191 | Registered: September 18, 2015 IP
Virtuoso
 posted October 21, 2019 09:18 AM Hide Post
I ran your example and I see no caret in the output.

WebFOCUS 8206, Unix, Windows

 Posts: 1853 | Location: New York City | Registered: December 30, 2015 IP
Platinum Member
 posted October 21, 2019 03:24 PM Hide Post
Well, let's try this again. I've taking a different approach to my challenge, and I am posting some sample code again, this time from the sample file Finance along with some samples of the results and what I need.

Here are my objectives:

• If Amount LE 1000 THEN turn it into a zero.
• If a column contains a zero as a result of #1, then turn the minimum result that is left in the column to a zero.

Use the resulting table to create the graph. The graph code will include a WHERE statement to remove any zeroes.

Here is the sample code without the minimum component.
```
DEFINE FILE FINANCE
AMOUNT/I4 = IF AMOUNT LE 1000 THEN '0' ELSE AMOUNT;
END

TABLE FILE FINANCE
SUM
FINANCE.TOP.AMOUNT
ACROSS FINANCE.TOP.YEAR
BY  FINANCE.TOP.ACCOUNT
ON TABLE SET ASNAMES ON
ON TABLE NOTOTAL
ON TABLE PCHOLD FORMAT HTML
ON TABLE SET HTMLEMBEDIMG ON
ON TABLE SET HTMLCSS ON
ON TABLE SET STYLE *
INCLUDE = IBFS:/EDA/EDASERVE/_EDAHOME/ETC/warm.sty,
\$
ENDSTYLE
END ```

Here are the results of the Finance file without any masking.

```YEAR 1982 1983
ACCOUNT
1000 1,294,611 1,430,903
1010 213,225 249,504
2000 5,639 818
4000 4,200 4,938
5000 23,758 28,052
6000 10,206 15,945
6500 45,643 35,158
6600 12,909 16,099
6900 1,743 1,264
7000 17,459 30,294
```

Here are the results after I have completed step one of the two-step masking.

``` YEAR 1982 1983
ACCOUNT
1000 1294611 1430903
1010 213225 249504
2000 5639 0
4000 4200 4938
5000 23758 28052
6000 10206 15945
6500 45643 35158
6600 12909 16099
6900 1743 1264
7000 17459 30294
```

For 1983, I need to turn 1,264 into a zero as it's the next minimum value. I can then eliminate zeroes from the graph part of the procedure using a WHERE statement.

Also, if 1983 already had two values turned to a zero, I would not need the minimum value turned to a zero.

Does that help explain things a little better?

WebFocus 8.2.04
WebFocus 8.2.04

 Posts: 191 | Registered: September 18, 2015 IP
Master
 posted October 21, 2019 08:07 PM Hide Post
Would something like this work?
```
TABLE FILE FINANCE
SUM COMPUTE NEW_AMOUNT/D12=IF AMOUNT LE 1000 THEN 0 ELSE AMOUNT;
BY YEAR
RANKED BY TOTAL LOWEST AMOUNT
BY ACCOUNT
ON TABLE HOLD AS HOLD1
END
-RUN

TABLE FILE HOLD1
SUM AMOUNT
COMPUTE ADJ_AMOUNT/D12=IF LAST NEW_AMOUNT EQ 0 AND RANK EQ 2 THEN 0 ELSE NEW_AMOUNT;
BY YEAR
BY RANK
BY ACCOUNT
ON TABLE SET PAGE-NUM OFF
ON TABLE SET STYLE *
INCLUDE = IBFS:/EDA/EDASERVE/_EDAHOME/ETC/warm.sty,\$
ENDSTYLE
END
-RUN
-EXIT
```

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

## Hallway

Prod: 8202M1
Test: 8202M4
Repository:

OS:

Outputs:

 Posts: 608 | Location: Salt Lake City, UT, USA | Registered: November 18, 2015 IP
Platinum Member
 posted October 22, 2019 12:54 PM Hide Post
Thank you so much! This works pretty well overall for us.

I am wondering if you have any suggestions on how to handle missing values. I'll read up on that again later as it's been awhile, but below is a dataset as it should appear on the final hold table.

``` Red-30
Blue-7
Green-Missing
Yellow-Missing
Orange-Missing
Pink-Missing
Purple-191 ```

So what happens in that case is that none of the missing values show up, and that's correct; however, the value of "7" for blue does not show up either.

Other than that, the procedure works very well, and I thank you for your generosity in sharing the code.

WebFocus 8.2.04
WebFocus 8.2.04

 Posts: 191 | Registered: September 18, 2015 IP