Focal Point Banner


As of December 1, 2020, Focal Point is retired and repurposed as a reference repository. We value the wealth of knowledge that's been shared here over the years. You'll continue to have access to this treasure trove of knowledge, for search purposes only.

Join the TIBCO Community
TIBCO Community is a collaborative space for users to share knowledge and support one another in making the best use of TIBCO products and services. There are several TIBCO WebFOCUS resources in the community.

  • From the Home page, select Predict: WebFOCUS to view articles, questions, and trending articles.
  • Select Products from the top navigation bar, scroll, and then select the TIBCO WebFOCUS product page to view product overview, articles, and discussions.
  • Request access to the private WebFOCUS User Group (login required) to network with fellow members.

Former myibi community members should have received an email on 8/3/22 to activate their user accounts to join the community. Check your Spam folder for the email. Please get in touch with us at community@tibco.com for further assistance. Reference the community FAQ to learn more about the community.


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

Read-Only Read-Only Topic
Go
Search
Notify
Tools
[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, 2011Report 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, 2003Report 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, 2011Report 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, 2012Report This Post
Expert
posted Hide Post
It's best to use LAST with a sort field to ensure data integrity.
 
Posts: 3132 | Location: Tennessee, Nashville area | Registered: February 23, 2005Report 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, 2011Report 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, 2005Report 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, 2012Report 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, 2011Report 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.07OS:LinuxOutputs:HTML, PDF, Excel, PPT
In Focus since 1984
Pity the lost knowledge of an old programmer!

 
Posts: 6347 | Location: 33°49'23.0"S, 151°11'41.0"E | Registered: October 31, 2006Report 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, 2005Report 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, 2011Report 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.07OS:LinuxOutputs:HTML, PDF, Excel, PPT
In Focus since 1984
Pity the lost knowledge of an old programmer!

 
Posts: 6347 | Location: 33°49'23.0"S, 151°11'41.0"E | Registered: October 31, 2006Report 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, 2011Report 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.07OS:LinuxOutputs:HTML, PDF, Excel, PPT
In Focus since 1984
Pity the lost knowledge of an old programmer!

 
Posts: 6347 | Location: 33°49'23.0"S, 151°11'41.0"E | Registered: October 31, 2006Report 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, 2011Report This Post
  Powered by Social Strata  

Read-Only Read-Only Topic

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

Copyright © 1996-2020 Information Builders