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     [SOLVED] Trim Field Length
Go
New
Search
Notify
Tools
Reply
  
[SOLVED] Trim Field Length
 Login/Join
 
Platinum Member
posted
I am pulling some data in to my report but I do not need to display the entire filed.

Example:
I am pulling a date/time field that appears like this:

2009/01/25 00:00:00.000

all I need is the first 10 digits from the left 2009/01/25

I also have another filed that pulls the employees pay status:

Exempt or Non-Exempt

I only need to show the first letter: E or N

Is there something like an "L/10" to tell the field to display 10 from the left?

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


prod: WF 7.7.03 platform IIS on Windows 2007, databases: Oracle, , MSSQL

 
Posts: 133 | Location: Orlando, FL | Registered: August 04, 2005Reply With QuoteReport This Post
Virtuoso
posted Hide Post
NEWDATE/YYMD = HDATE(END_DATE_TIME, 'YYMD');


In Focus since 1993. WebFOCUS 7.7.03 Win 2003
 
Posts: 1903 | Location: San Antonio | Registered: February 28, 2005Reply With QuoteReport This Post
Virtuoso
posted Hide Post
There is the EDIT function where you basically use a mask to retrieve the desired characters from a string, so you could EDIT (fieldname,'9999999999'); where the '9' indicates to include the character in that position. (A $ eliminates that character, any other character is inserted into the resulting string) Additional info is in the documentation.

However, if this is a Date-Time field, you would have to use the HDATE function to retrieve the date portion of the field. EDIT will not work for this. (Unless it is just an alpha representation of a data-time stamp.)


Regards,

Darin



In FOCUS since 1991
WF Server: 7.7.04 on Linux and Z/OS, ReportCaster, Self-Service, MRE, Java, Flex
Data: DB2/UDB, Adabas, SQL Server Output: HTML,PDF,EXL2K/07, PS, AHTML, Flex
WF Client: 77 on Linux w/Tomcat
 
Posts: 2298 | Location: Salt Lake City, Utah | Registered: February 02, 2007Reply With QuoteReport This Post
Platinum Member
posted Hide Post
OK. So for my Employee Pay Status field I want to cut off the last 10 charators so I just show the first letter:

EDIT(ALL.WF_VP_EMPLOYEE.WF_VP_EMPLOYEE.PAYRULENAME,'9$$$$$$$$$$')

Now where in the report does it go?


prod: WF 7.7.03 platform IIS on Windows 2007, databases: Oracle, , MSSQL

 
Posts: 133 | Location: Orlando, FL | Registered: August 04, 2005Reply With QuoteReport This Post
Virtuoso
posted Hide Post
Well, you're creating a new virtual field, so it would go in a DEFINE or COMPUTE statement. You do have some documentation available, I hope?


Regards,

Darin



In FOCUS since 1991
WF Server: 7.7.04 on Linux and Z/OS, ReportCaster, Self-Service, MRE, Java, Flex
Data: DB2/UDB, Adabas, SQL Server Output: HTML,PDF,EXL2K/07, PS, AHTML, Flex
WF Client: 77 on Linux w/Tomcat
 
Posts: 2298 | Location: Salt Lake City, Utah | Registered: February 02, 2007Reply With QuoteReport This Post
Virtuoso
posted Hide Post
To truncate the field, either (a) print a defined field rather than the original, or (b) use "dynamic reformatting" to truncate it:

a:
Define file xxx
RULEINIT/A1=EDIT(PAYRULENAME,'9');
end
table file xxx
print RULEINIT
...

b:
table file xxx
print PAYRULENAME/A1
...


- Jack Gross
WF through 8.1.05
 
Posts: 1925 | Location: NYC | In FOCUS since 1983 | Registered: January 11, 2005Reply With QuoteReport This Post
Expert
posted Hide Post
Download the USING FUNCTIONS manual, one of the basic set of 4, commit it to memory, ..
the SUBSTR function works on ORACLE and other RDBMS better than the EDIT function.
and is a cool combination of the LEFT, RIGHT and MID excel functions...




In Focus since 1979///7706m/5 ;wintel 2008/64;OAM security; Oracle db, ///MRE/BID
 
Posts: 3811 | Location: Manhattan | Registered: October 28, 2003Reply With QuoteReport This Post
Platinum Member
posted Hide Post
quote:
Originally posted by susannah:
Download the USING FUNCTIONS manual, one of the basic set of 4, commit it to memory, ..
the SUBSTR function works on ORACLE and other RDBMS better than the EDIT function.
and is a cool combination of the LEFT, RIGHT and MID excel functions...


I have this book. On page 8-44 its says:
HDATE(value, 'YYMD' [, outfield])

It says you only have to spec the outfield if using as maintain.

I want to use this right in the report so I am adding this to the report:

PREVSTART/YYMD = HDATE(WF_VP_EMPLOYEE.WF_VP_EMPLOYEE.PREVPAYPERIODSTART, 'YYMD')

But when I run the report I get: >HDATE< parsing error, so I am guessing it does not go directly in the source of the report.

If I put it in the same define file as the empstatus (which I have working, thanks everyone), I get a 252 computation error.

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


prod: WF 7.7.03 platform IIS on Windows 2007, databases: Oracle, , MSSQL

 
Posts: 133 | Location: Orlando, FL | Registered: August 04, 2005Reply With QuoteReport This Post
Virtuoso
posted Hide Post
Greg,

maybe some basic training would help a lot.
But this is what you have to do

DEFINE FILE WHATEVER
NEWDATE/YYMD=HDATE(PREVPAYPERIODSTART, 'YYMD');
SHORTNAME/A1=EDIT(PAYRULENAME,'9');
END
TABLE FILE WHATEVER
PRINT 
fields you need
NEWDATE
BY SHORTNAME
END






Frank

prod: WF 7.6.10 platform Windows,
databases: msSQL2000, msSQL2005, RMS, Oracle, Sybase,IE7
test: WF 7.6.10 on the same platform and databases,IE7

 
Posts: 2387 | Location: Amsterdam, the Netherlands | Registered: December 03, 2006Reply With QuoteReport This Post
Master
posted Hide Post
You need the HDATE to convert your date time field, but on your alpha field you can change the printed size in the report, you don't need the edit.

TABLE FILE CAR
PRINT CAR/A1

BY COUNTRY 


ON TABLE PCHOLD FORMAT PDF
END
  


Pat
WF 7.6.8, AIX, AS400, NT
AS400 FOCUS, AIX FOCUS,
Oracle, DB2, JDE, Lotus Notes
 
Posts: 755 | Location: TX | Registered: September 25, 2007Reply With QuoteReport This Post
Virtuoso
posted Hide Post
If you NEVER need the time part of the date-time field, the easiest way for every report and/or user is to modify the master file, so that the ACTUAL keyword for this field does not read HYYMDs but DATE, and the USAGE keyword reads YYMD in stead of HYYMDs. This will return only the date part and leaves out the time. You'll not have to use date conversion routines or something like that, just referencing the field gives you the date only.


GamP

- Using AS 8.2.01 on Windows 7 - IE11.
in Focus since 1988
 
Posts: 1960 | Location: Netherlands | Registered: September 25, 2007Reply With QuoteReport This Post
  Powered by Social Strata  
 

Focal Point    Focal Point Forums  Hop To Forum Categories  WebFOCUS/FOCUS Forum on Focal Point     [SOLVED] Trim Field Length

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