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.



Read-Only Read-Only Topic
Go
Search
Notify
Tools
Removing digits
 Login/Join
 
Latigresa
Platinum Member
posted
OK...I'm sure this is a very easy thing to do but I have no clue...I have a field that has 2 and 4 numbers in it. I need to trim it to only the 2 first numbers. In otherwords, I need to drop the last 2 numbers even though some only have 2 numbers in it in which case I don't want to do anything to those.

Thank you,
Ana


WEBFOCUS 7.6.4
Server: WINXP
 
Posts: 121 | Registered: September 20, 2007Report This Post
Francis Mariani
Expert
posted Hide Post
If it's a left-justified alpha field, you can do the following:
COMPUTE FIELD2/A2 = EDIT(FIELD1,'99$$');

or the following preferable method:
COMPUTE FIELD2/A2 = SUBSTR(4, FIELD1, 1, 2, 2, 'A2');

If it's a numeric field, you would first have to convert it to Integer (if it isn't already), and then use the EDIT or SUBSTR function:
COMPUTE FIELD1I/I4 = FIELD1;

Then
COMPUTE FIELD2/A2  = EDIT(FIELD1I,'$$99');

or:
COMPUTE FIELD2/A2  = SUBSTR(4, FIELD1I, 3, 4, 2, 'A2');


In the EDIT function, the mask character '9' means copy the corresponding character in the field, '$' means ignore.

Syntax for substring function:
SUBSTR(inlength, parent, start, end, sublength, outfield)


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
GinnyJakes
Expert
posted Hide Post
How about this:

COMPUTE or DEFINE
NUMFLD/I4=IF NUMFLD LT 100 THEN NUMFLD ELSE IF NUMFLD LT 1000 THEN NUMFLD/10 ELSE NUMFLD/1000;

If the field is not integer to start with, you can use an equality to make it so.


Ginny
---------------------------------
Prod: WF 7.7.01 Dev: WF 7.6.9-11
Admin, MRE,self-service; adapters: Teradata, DB2, Oracle, SQL Server, Essbase, ESRI, FlexEnable, Google
 
Posts: 2723 | Location: Ann Arbor, MI | Registered: April 05, 2006Report This Post
Latigresa
Platinum Member
posted Hide Post
Thanks Francis..That did it!!! BUT I need it to be a define now that i think of it because once I do this I need to exclude all that equal 50. :-( The compute did strip it down to 2 but now I can't use it in my where clause.

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


WEBFOCUS 7.6.4
Server: WINXP
 
Posts: 121 | Registered: September 20, 2007Report This Post
Frans
Guru
posted Hide Post
You can also use these COMPUTE's in a define, but keep in mind that this will only cause effect on record level while COMPUTE works on aggregated data.

Do you only use this for your where statement? If so, post you're WHERE statement here.


Test: WF 8.2
Prod: WF 8.2
DB: Progress, REST, IBM UniVerse/UniData, SQLServer, MySQL, PostgreSQL, Oracle, Greenplum, Athena.
 
Posts: 454 | Location: Europe | Registered: February 05, 2007Report This Post
Latigresa
Platinum Member
posted Hide Post
I did a define using the same data you gave me Francis and it worked great!!!

Thanks A lot!!!


WEBFOCUS 7.6.4
Server: WINXP
 
Posts: 121 | Registered: September 20, 2007Report This Post
Francis Mariani
Expert
posted Hide Post
Don't forget that this kind of manipulation in a DEFINE on a dbms table will not generate efficient SQL.


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
Latigresa
Platinum Member
posted Hide Post
I'm not using SQL thank goodness.. Smiler


WEBFOCUS 7.6.4
Server: WINXP
 
Posts: 121 | Registered: September 20, 2007Report This Post
Francis Mariani
Expert
posted Hide Post
BTW, does Latigresa mean "The tigress"? Smiler


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
Latigresa
Platinum Member
posted Hide Post
Yes it does... Wink

Ana


WEBFOCUS 7.6.4
Server: WINXP
 
Posts: 121 | Registered: September 20, 2007Report This Post
Francis Mariani
Expert
posted Hide Post
Are you dangerous? Razzer


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
Piipster
Guru
posted Hide Post
quote:
If it's a left-justified alpha field, you can do the following:

COMPUTE FIELD2/A2 = EDIT(FIELD1,'99$$');

or the following preferable method:

COMPUTE FIELD2/A2 = SUBSTR(4, FIELD1, 1, 2, 2, 'A2');



I would us EDIT over SUBSTR in most cases. EDIT is an Internal function built into WebFOCUS. SUBSTR is an External Function stored in an external library that must be accessed. The only time I use SUBSTR is if the string I want is very long.


ttfn, kp


Access to most releases from R52x, on multiple platforms.
 
Posts: 346 | Location: Melbourne Australia | Registered: April 15, 2003Report This Post
Tony A
Expert
posted Hide Post
Bucket of cold water for Francis!
 
Posts: 5694 | Location: United Kingdom | Registered: April 08, 2004Report This Post
Francis Mariani
Expert
posted Hide Post
Tony, good morning!
 
Posts: 10577 | Location: Toronto, Ontario, Canada | Registered: April 27, 2005Report This Post
ira
Platinum Member
posted Hide Post
My preference is GinnyJakes for the sake of easier to maintain. Although the other recommendations are equally as nice.
Guess Latigresa is roaring now Wink

Ira wf 5.3.6 aix533


aix-533,websphere 5.1.1,apache-2.0,
wf 538(d), 537 (p),
==============
7.6.11 (t) aix 5312
websphere 6.1.19
apache 2.0
 
Posts: 195 | Registered: October 27, 2006Report This Post
Latigresa
Platinum Member
posted Hide Post
Sorry...I just got logged in again and read all the messages...Not dangerous at all..lol... Music...and YES I was ROARING and didn't want to stop till I got done with all of it...I'm done now for the most part...

THANKS to all of you that helped me out!!! Smiler


WEBFOCUS 7.6.4
Server: WINXP
 
Posts: 121 | Registered: September 20, 2007Report This Post
Francis Mariani
Expert
posted Hide Post
I was asking if you were dangerous because "Removing digits" sounded like you wanted to bite somebody's fingers off Red Face


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
Latigresa
Platinum Member
posted Hide Post
Well usually by the time I place a question on here it is because I am extremely frustrated from not being able to get it to work on my own and that's probably why I use some of the words I do cause I am thinking bad thoughts. but tryingto place a question on here without letting everyone know at the same time. Hahaha...

Anyhows...

Merry Christmas and Happy New Year to all!!!


WEBFOCUS 7.6.4
Server: WINXP
 
Posts: 121 | Registered: September 20, 2007Report This Post
  Powered by Social Strata  

Read-Only Read-Only Topic


Copyright © 1996-2020 Information Builders