Focal Point Banner
Community Center Education Summit Technical Support User Groups
Let's Get Social!

Facebook Twitter LinkedIn YouTube
Go
New
Search
Notify
Tools
Reply
  
Removing digits
 Login/Join
 
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, 2007Reply With QuoteReport This Post
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, 2005Reply With QuoteReport This Post
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, 2006Reply With QuoteReport This Post
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, 2007Reply With QuoteReport This Post
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: 455 | Location: Europe | Registered: February 05, 2007Reply With QuoteReport This Post
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, 2007Reply With QuoteReport This Post
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, 2005Reply With QuoteReport This Post
Platinum Member
posted Hide Post
I'm not using SQL thank goodness.. Smiler


WEBFOCUS 7.6.4
Server: WINXP
 
Posts: 121 | Registered: September 20, 2007Reply With QuoteReport This Post
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, 2005Reply With QuoteReport This Post
Platinum Member
posted Hide Post
Yes it does... Wink

Ana


WEBFOCUS 7.6.4
Server: WINXP
 
Posts: 121 | Registered: September 20, 2007Reply With QuoteReport This Post
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, 2005Reply With QuoteReport This Post
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, 2003Reply With QuoteReport This Post
Expert
posted Hide Post
Bucket of cold water for Francis!
 
Posts: 5694 | Location: United Kingdom | Registered: April 08, 2004Reply With QuoteReport This Post
Expert
posted Hide Post
Tony, good morning!
 
Posts: 10577 | Location: Toronto, Ontario, Canada | Registered: April 27, 2005Reply With QuoteReport This Post
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, 2006Reply With QuoteReport This Post
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, 2007Reply With QuoteReport This Post
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, 2005Reply With QuoteReport This Post
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, 2007Reply With QuoteReport This Post
  Powered by Social Strata  
 


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