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:
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 PAGE-NUM NOLEAD 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,
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
Thank you for your reply.
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?
I ran your example and I see no caret in the output.
WebFOCUS 8206, Unix, Windows
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:
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 PAGE-NUM NOLEAD 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?
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 -EXITThis message has been edited. Last edited by: Hallway,
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.
|Powered by Social Strata|