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]LAST function for D20.2 variable
Go
New
Search
Notify
Tools
Reply
  
[closed]LAST function for D20.2 variable
 Login/Join
 
Gold member
posted
Hello Everyone,

I am trying to use the last function for a numeric field.
The following is the sample code:

-SET &ECHO=ALL;
TABLE FILE CAR
PRINT
CAR.ORIGIN.COUNTRY
COMPUTE A123/A20 = IF COUNTRY EQ LAST COUNTRY THEN '' ELSE COUNTRY;
CAR.COMP.CAR
COMPUTE DC/D20.2 = IF DEALER_COST EQ LAST DEALER_COST THEN '' ELSE DEALER_COST;
CAR.BODY.RETAIL_COST
ON TABLE NOTOTAL
ON TABLE SET STYLE *
ENDSTYLE
END

The requirement is such that the users need no data if the previous numeric value is the same. (they dont want zero.. they want to see a blank field).
I have also tried to set a variable to blank and call it in the compute/define, but I still get IF THEN ELSE syntax error.

Any suggestions will be welcome.
Thanks for reading.

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


WebFOCUS 7.7.02Windows
Output Formats: Excel, HTML, PDF
 
Posts: 82 | Registered: February 23, 2011Reply With QuoteReport This Post
Platinum Member
posted Hide Post
You can't use '' for a blank in a numeric field, the field format has to be alphanumeric for that to work.

Try this:

SET NODATA = ''
TABLE FILE CAR
PRINT
COUNTRY
COMPUTE A123/A20 = IF COUNTRY EQ LAST COUNTRY THEN '' ELSE COUNTRY;
CAR
COMPUTE DC/D20.2 MISSING ON= IF DEALER_COST EQ LAST DEALER_COST THEN MISSING ELSE DEALER_COST;
RETAIL_COST
ON TABLE NOTOTAL
ON TABLE SET STYLE *
ENDSTYLE
END


WF 7.6.11
Output: HTML, PDF, Excel
 
Posts: 123 | Location: UK | Registered: October 09, 2003Reply With QuoteReport This Post
Gold member
posted Hide Post
Thanks Tewy for the reply.
It works fine for the CAR file but when I actually implement it in my code, it gives me a 8 digit garbage value instead of blank.


WebFOCUS 7.7.02Windows
Output Formats: Excel, HTML, PDF
 
Posts: 82 | Registered: February 23, 2011Reply With QuoteReport This Post
Guru
posted Hide Post
Then it has to do with your actual data type. Can we see your master file description for that field(assuming it is a field in a master file)? Also, is the field stored externally in a database? We may need to see that format as well...


WebFOCUS 7.7.03/8.0.08
Dev Studio 7.7.03/8.0.08
App Studio 8.0.08
Windows 7
ALL Outputs
 
Posts: 402 | Location: Upland, IN | Registered: June 08, 2012Reply With QuoteReport This Post
Expert
posted Hide Post
It's best to use LAST with a sort field to ensure data integrity.
 
Posts: 3066 | Location: Middle Tennessee [8204M Gen48 in Test&Prod] | Registered: February 23, 2005Reply With QuoteReport This Post
Gold member
posted Hide Post
Hello J and Doug,

The master file description for the field is as follows:

FIELDNAME=OPENING_BALANCE, ALIAS=OPENING_BALANCE, USAGE=P21.4, ACTUAL=P10,
MISSING=ON, $

The type from the Database is NUMBER(19,4).

Thanks


WebFOCUS 7.7.02Windows
Output Formats: Excel, HTML, PDF
 
Posts: 82 | Registered: February 23, 2011Reply With QuoteReport This Post
Expert
posted Hide Post
As long as your data is in the correct order, how about suppressing the zero with a numeric display option - "S" for Zero suppress:
TABLE FILE CAR
PRINT
CAR.ORIGIN.COUNTRY
COMPUTE A123/A20 = IF COUNTRY EQ LAST COUNTRY THEN '' ELSE COUNTRY;
CAR.COMP.CAR
COMPUTE DC/D20.2S = IF DEALER_COST EQ LAST DEALER_COST THEN 0 ELSE DEALER_COST;
CAR.BODY.RETAIL_COST
ON TABLE NOTOTAL
END


Francis


Give me code, or give me retirement. In FOCUS since 1991

Production: WF 7.7.05M, Dev Studio, BID, MRE, WebSphere, DB2 / Test: WF 8.1.05M, App Studio, BI Portal, Report Caster, jQuery, HighCharts, Apache Tomcat, MS SQL Server
 
Posts: 10577 | Location: Toronto, Ontario, Canada | Registered: April 27, 2005Reply With QuoteReport This Post
Guru
posted Hide Post
Ah yes, the packed decimal...
I forgot about Francis's suggestion. That will probably do.


WebFOCUS 7.7.03/8.0.08
Dev Studio 7.7.03/8.0.08
App Studio 8.0.08
Windows 7
ALL Outputs
 
Posts: 402 | Location: Upland, IN | Registered: June 08, 2012Reply With QuoteReport This Post
Gold member
posted Hide Post
Thanks a lot Francis and J.
Franscis's suggestion of supressing it worked fine.


WebFOCUS 7.7.02Windows
Output Formats: Excel, HTML, PDF
 
Posts: 82 | Registered: February 23, 2011Reply With QuoteReport This Post
Expert
posted Hide Post
So I guess zero is not a valid value in your data ?


Waz...

Prod:WebFOCUS 7.6.10/8.1.04Upgrade:WebFOCUS 8.2.06OS:LinuxOutputs:HTML, PDF, Excel, PPT
In Focus since 1984
Know The Code

 
Posts: 6252 | Location: 33°49'23.0"S, 151°11'41.0"E | Registered: October 31, 2006Reply With QuoteReport This Post
Expert
posted Hide Post
ah! Frowner


Francis


Give me code, or give me retirement. In FOCUS since 1991

Production: WF 7.7.05M, Dev Studio, BID, MRE, WebSphere, DB2 / Test: WF 8.1.05M, App Studio, BI Portal, Report Caster, jQuery, HighCharts, Apache Tomcat, MS SQL Server
 
Posts: 10577 | Location: Toronto, Ontario, Canada | Registered: April 27, 2005Reply With QuoteReport This Post
Gold member
posted Hide Post
The users only want to see a blank if the previous numberic value is the same.


WebFOCUS 7.7.02Windows
Output Formats: Excel, HTML, PDF
 
Posts: 82 | Registered: February 23, 2011Reply With QuoteReport This Post
Expert
posted Hide Post
But can you have two numeric values of zero, therefore the preceding one whould be blank ?

If this is the case, then zero suppress is not the way.


Waz...

Prod:WebFOCUS 7.6.10/8.1.04Upgrade:WebFOCUS 8.2.06OS:LinuxOutputs:HTML, PDF, Excel, PPT
In Focus since 1984
Know The Code

 
Posts: 6252 | Location: 33°49'23.0"S, 151°11'41.0"E | Registered: October 31, 2006Reply With QuoteReport This Post
Gold member
posted Hide Post
I have checked the data for various date ranges and I currently dont see any 0's in the values for it.
There has to be some amount in the field.

So the method of suppressing holds till there is change in the data.


WebFOCUS 7.7.02Windows
Output Formats: Excel, HTML, PDF
 
Posts: 82 | Registered: February 23, 2011Reply With QuoteReport This Post
Expert
posted Hide Post
Well thats the important part, the analysis.

Fingers crossed that zeros won't creep in.


Waz...

Prod:WebFOCUS 7.6.10/8.1.04Upgrade:WebFOCUS 8.2.06OS:LinuxOutputs:HTML, PDF, Excel, PPT
In Focus since 1984
Know The Code

 
Posts: 6252 | Location: 33°49'23.0"S, 151°11'41.0"E | Registered: October 31, 2006Reply With QuoteReport This Post
Gold member
posted Hide Post
Keeping the fingers crossed. Smiler


WebFOCUS 7.7.02Windows
Output Formats: Excel, HTML, PDF
 
Posts: 82 | Registered: February 23, 2011Reply With QuoteReport This Post
  Powered by Social Strata  
 

Focal Point    Focal Point Forums  Hop To Forum Categories  WebFOCUS/FOCUS Forum on Focal Point     [closed]LAST function for D20.2 variable

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