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     DATES from Alpha to MDYY

Read-Only Read-Only Topic
Go
Search
Notify
Tools
DATES from Alpha to MDYY
 Login/Join
 
<Umar Farook S>
posted
Hi I am having a date input in alphanumeric(A9)format like 15-Oct-07 but in my report O/P i need to diplay like 10/15/2007.
I tried the below code and it works fine.But when i give my field name it is not working.

DEFINE FILE CAR
TEST_DATE/A9='15-Oct-07';
Get_Month/A3=EDIT(TEST_DATE,'$$$999');
Month/A2= DECODE Get_Month ('Jan' '01' 'Feb' '02' 'Mar' '03' 'Apr' '04' 'May' '05' 'Jun' '06'
'Jul' '07' 'Aug' '08' 'Sep' '09' 'Oct' '10' 'Nov' '11' 'Dec' '12' ELSE ' ');
Day/A2=EDIT(TEST_DATE,'99');
Year/A2=EDIT(TEST_DATE,'$$$$$$$99');
ALPHA_DATE/A8MDYY = Month | Day |'20'| Year;
FINAL_DATE/MDYY = ALPHA_DATE;
END

TABLE FILE CAR
PRINT COUNTRY TEST_DATE Month Day Year ALPHA_DATE FINAL_DATE
END
-RUN
-EXIT
 
Report This Post
Expert
posted Hide Post
Is it a column from a table or is it a Dialogue Manager variable?

If it's a column of a table, what error do you get? Perhaps the size of the column is larger than 9 characters. If so, use EDIT or SUBSTR to extract the first 9 characters.

If it's a Dialogue Manager variable, this should work:

-SET &DATE1 = '15-Oct-07';

DEFINE FILE CAR
TEST_DATE/A9='&DATE1';
GET_MONTH/A3=EDIT(TEST_DATE,'$$$999');
MONTH/A2= DECODE Get_Month ('Jan' '01' 'Feb' '02' 'Mar' '03' 'Apr' '04' 'May' '05' 'Jun' '06'
'Jul' '07' 'Aug' '08' 'Sep' '09' 'Oct' '10' 'Nov' '11' 'Dec' '12' ELSE ' ');
DAY/A2=EDIT(TEST_DATE,'99');
YEAR/A2=EDIT(TEST_DATE,'$$$$$$$99');
ALPHA_DATE/A8MDYY = Month | Day |'20'| Year;
FINAL_DATE/MDYY = ALPHA_DATE;
END

TABLE FILE CAR
PRINT
COUNTRY NOPRINT
TEST_DATE MONTH DAY YEAR ALPHA_DATE FINAL_DATE
END
-RUN


What error do you get exactly?


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
<Umar Farook S>
posted
Actually it is a column read from external CSV file.In the file it is in 1-Oct-07,15-Oct-07 etc etc.. format.So inorder to read it i have used A9 in master file.

DEFINE FILE ONE
TEST_DATE/A9=TDate;
Get_Month/A3=EDIT(TEST_DATE,'$$$999');
Month/A2= DECODE Get_Month ('Jan' '01' 'Feb' '02' 'Mar' '03' 'Apr' '04' 'May' '05' 'Jun' '06'
'Jul' '07' 'Aug' '08' 'Sep' '09' 'Oct' '10' 'Nov' '11' 'Dec' '12' ELSE ' ');
Day/A2=EDIT(TEST_DATE,'99');
Year/A2=EDIT(TEST_DATE,'$$$$$$$99');
ALPHA_DATE/A8MDYY = Month | Day |'20'| Year;
FINAL_DATE/MDYY = ALPHA_DATE;
END

TABLE FILE ONE
PRINT
Get_Month
Month
Day
Year
ALPHA_DATE
FINAL_DATE
BY ASSET_ID
END
-RUN
-EXIT

:::::error:::::
0 ERROR AT OR NEAR LINE 353 IN PROCEDURE MEMFEX FOCEXEC *
(FOC258) FIELDNAME OR COMPUTATIONAL ELEMENT NOT RECOGNIZED: TDate
0 ERROR AT OR NEAR LINE 355 IN PROCEDURE MEMFEX FOCEXEC *
(FOC258) FIELDNAME OR COMPUTATIONAL ELEMENT NOT RECOGNIZED: TEST_DATE
0 ERROR AT OR NEAR LINE 357 IN PROCEDURE MEMFEX FOCEXEC *
(FOC258) FIELDNAME OR COMPUTATIONAL ELEMENT NOT RECOGNIZED: Get_Month
0 ERROR AT OR NEAR LINE 363 IN PROCEDURE MEMFEX FOCEXEC *
(FOC258) FIELDNAME OR COMPUTATIONAL ELEMENT NOT RECOGNIZED: TEST_DATE
0 ERROR AT OR NEAR LINE 365 IN PROCEDURE MEMFEX FOCEXEC *
(FOC258) FIELDNAME OR COMPUTATIONAL ELEMENT NOT RECOGNIZED: Month
0 ERROR AT OR NEAR LINE 367 IN PROCEDURE MEMFEX FOCEXEC *
(FOC258) FIELDNAME OR COMPUTATIONAL ELEMENT NOT RECOGNIZED: ALPHA_DATE
0 ERROR AT OR NEAR LINE 405 IN PROCEDURE MEMFEX FOCEXEC *
(FOC003) THE FIELDNAME IS NOT RECOGNIZED: Get_Month
BYPASSING TO END OF COMMAND
(FOC009) INCOMPLETE REQUEST STATEMENT
 
Report This Post
Guru
posted Hide Post
Ali - is this you? I recognize this code. Send me your folder and focexec and I'll take a look

Carol Smiler



WebFOCUS 7.6.6/TomCat/Win2k3
 
Posts: 428 | Location: Springfield, MA | Registered: May 07, 2003Report This Post
<Umar Farook S>
posted
Sure Carol yes its me
 
Report This Post
Expert
posted Hide Post
Check your Master. It appears that file ONE does not have a field named TDate.


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
<Umar Farook S>
posted
TDate field is present both in MASTER and the ONE file.
 
Report This Post
Virtuoso
posted Hide Post
Could you post your master file for ONE? About the only explanation for the error you're getting is the one offered by Francis. Possibly you have more than one copy of it in your server path.


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, 2007Report This Post
Expert
posted Hide Post
Umar (or Ali?),

If your date is coming in as 1-Oct-07 or 15-Oct-07 then a fixed length field substring is not going to help you one bit (Get_Month/A3=EDIT(TEST_DATE,'$$$999')) as in the first date the value for Get_Month would be "ct-" and the second would be OK.

Try using GETTOK for the components. I would have suggested using STRREP to change the hyphens to slashes but STRREP objects to the '-' as a parameter as either change string.

-SET &Date = '1-Oct-07';
-SET &Umar_Day1 = GETTOK(&Date, 9, 1, '-', 2, 'A2');
-SET &Umar_Mth1 = GETTOK(&Date, 9, 2, '-', 3, 'A3');
-SET &Umar_Yer1 = GETTOK(&Date, 9, 3, '-', 2, 'A2');
-SET &Date = '15-Oct-07';
-SET &Umar_Day2 = GETTOK(&Date, 9, 1, '-', 2, 'A2');
-SET &Umar_Mth2 = GETTOK(&Date, 9, 2, '-', 3, 'A3');
-SET &Umar_Yer2 = GETTOK(&Date, 9, 3, '-', 2, 'A2');
-? &Umar

T



In FOCUS
since 1986
WebFOCUS Server 8.2.01M, thru 8.2.07 on Windows Svr 2008 R2  
WebFOCUS App Studio 8.2.06 standalone on Windows 10 
 
Posts: 5694 | Location: United Kingdom | Registered: April 08, 2004Report This Post
Expert
posted Hide Post
Sadly, the function CHGDAT cannot convert from 03-OCT-07 to 20071003, just the other way around. It would have made your conversion a lot simpler.


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
Virtuoso
posted Hide Post
Who or what creates the flat file?

Is it a possibility to go back to the source and let them create the flat file with

20071015
20071001
etc




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, 2006Report This Post
<Umar Farook S>
posted
Thanks Very Much Tony
GETTOK worked fine.

Thanks a TON!!
 
Report This Post
<Umar Farook S>
posted
Its an extract from another system
 
Report This Post
Platinum Member
posted Hide Post
Fixed length it is not, so standard editing would not work here. Also as pointed out by Tony using a '-' is not a recommended practice b/c given the wrong scenario it could be misunderstood to be a minus sign in a calculation.
Fixed format would be the best case to work with. Tony's suggestion right on the mark.

Ira WF 5.3.6 (aix)


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
Virtuoso
posted Hide Post
Umar

You might be able to do the extract from the other system with WF.
If that field is a real date field in the database you would be in charge and be able to convert it at the extraction moment.

Just a thought.




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, 2006Report This Post
<Umar Farook S>
posted
I am not incharge of the extraction process. I just get the CSV file and i start working on it.Can make this as a suggestion to other members incharge of making the extract from that system.It will also be helpfull in future.

Thanks for your thought.
 
Report This Post
Expert
posted Hide Post
Umar,

I am glad that you have managed to progress your problem. Smiler

T



In FOCUS
since 1986
WebFOCUS Server 8.2.01M, thru 8.2.07 on Windows Svr 2008 R2  
WebFOCUS App Studio 8.2.06 standalone on Windows 10 
 
Posts: 5694 | Location: United Kingdom | Registered: April 08, 2004Report This Post
Master
posted Hide Post
I wonder if someone can explain the following. If you do the following, it works:
DEFINE FILE XXX
DATE1/MDYY='15 OCT 07';
END


But this doesn't work.

Also, to get this to work, one has to make sure the DEFCENT and YRTHRESH are properly set.
DEFINE FILE XXX
DATE1/A9='15 OCT 07';
DATE2/MDYY=DATE1;
END


It seems if I use a date literal, I can convert the literal to a smart date. I don't have to do all the DECODE, Concatenation, etc. But if I use a field that contains a date literal, I cannot, unless I parse the components. I don't know about you guys, but this seems like a bug to me.

In Umar's problem, say TDATE contains '15-OCT-07', then the following should work.
TEST_DATE/A9=CTRAN(9,TDATE,45,32,'A9');
FINAL_DATE/MDYY=TEST_DATE;


Seems to me, that if TEST_DATE contains '15 OCT 07', there should be no difference between following:
FDATE1/MDYY='15 OCT 07';
FDATE2/MDYY=TEST_DATE;

yet FDATE1 will work while FDATE2 fails.

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


In FOCUS since 1985. Prod WF 8.0.08 (z90/Suse Linux) DB (Oracle 11g), Self Serv, Report Caster, WebServer Intel/Linux.
 
Posts: 975 | Location: Oklahoma City | Registered: October 27, 2006Report This Post
<Umar Farook S>
posted
I too wonder when date is directly passed it works.But when a field containing date is passed it doesn't.
 
Report 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     DATES from Alpha to MDYY

Copyright © 1996-2020 Information Builders