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     [SOLVED] Converting date to Jan-14 (Mtr)

Read-Only Read-Only Topic
Go
Search
Notify
Tools
[SOLVED] Converting date to Jan-14 (Mtr)
 Login/Join
 
Platinum Member
posted
Hi All.. I have a DATEing issue
I have two fields
YR/A2='14';
MO/A2='01';
Need to Display as Jan-14
I can convert MO to Jan using Mtr format..
Then I would like to concatenate Month (Jan) with - to Year .. Like Jan-14, Feb-14 etc..
WF does not convert Mtr Mon to Alpha and does not concatenate .. gives format error..

How Do I convert Mtr Month (Example: Jan) to Character field so that I concatenate with '-' and Year..

Thanks

This message has been edited. Last edited by: <Kathryn Henning>,


Prod/Dev/Test: WF 8.1.5 on (Windows Server 2012 R2 )
SandBox: WebFocus Server 8.1.5 on Windows Server 2008 R2
WebFOCUS App Studio 8.1.5 and Developer Studio 8.1.5 on Windows 7
 
Posts: 134 | Location: USA | Registered: August 21, 2008Report This Post
Expert
posted Hide Post
The proper answer would be to convert your data to a date column and then use the DATETRAN function to get YY-Mt but because I believe DATETRAN is quite tedious to use, I would user the non-proper solution and compute the three character name:

TABLE FILE CAR
PRINT
COMPUTE YR/A2='14';
COMPUTE MO/A2='01';

COMPUTE MOTr/A3 = DECODE MO ('01' 'Jan', '02' 'Feb', '03' 'Mar', '04' 'Apr', '05' 'May', '06' 'Jun'
                             '07' 'Jul', '08' 'Aug', '09' 'Sep', '10' 'Oct', '11' 'Nov', '12' 'Dec' ELSE '???');

COMPUTE MOTr_YY/A6 = MOTr | '-' | YR;
BY COUNTRY NOPRINT
WHERE RECORDLIMIT EQ 1
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
Silver Member
posted Hide Post
using DATETRAN:

DEFINE FILE GGSALES
 Date1/YYMD=DATE;
Date1A/MYY = Date1;
Date2/A40=DATETRAN(Date1A,  '(MY)', '(-t)', 'EN', 40, 'A40');
 END
 TABLE FILE GGSALES
 SUM DOLLARS
 BY Date1A NOPRINT
BY Date2
  END
-RUN


WebFOCUS 8.0.08 - BI Portal, Developer Studio, App Studio, Excel, PDF, Active Formats and HTML5
Windows, All Outputs
 
Posts: 40 | Registered: April 02, 2014Report This Post
Virtuoso
posted Hide Post
Using alpha information as you provide :
DEFINE FILE CAR
YR     /A2  = '14';
MO     /A2  = '01';
YRMODY /A4YM = YR | MO;
DTE /A20 = DATETRAN(DATECVT(YRMODY, 'A4YM','MY'), '(MY)', '(-t)', 'EN', 20, 'A20') ;
END
TABLE FILE CAR
PRINT 
DTE
BY COUNTRY
WHERE RECORDLIMIT EQ 1;
WHERE READLIMIT EQ 1;
END


Also work with numeric value
DEFINE FILE CAR
YR     /I2  = 14;
MO     /I2  = 01;
YRMODY /I4YM = YR * 100 + MO;
DTE /A20 = DATETRAN(DATECVT(YRMODY, 'I4YM','MY'), '(MY)', '(-t)', 'EN', 20, 'A20') ;
END
TABLE FILE CAR
PRINT 
DTE
BY COUNTRY
WHERE RECORDLIMIT EQ 1;
WHERE READLIMIT EQ 1;
END


WF versions : Prod 8.2.04M gen 33, Dev 8.2.04M gen 33, OS : Windows, DB : MSSQL, Outputs : HTML, Excel, PDF
In Focus since 2007
 
Posts: 2409 | Location: Montreal Area, Qc, CA | Registered: September 25, 2013Report This Post
Virtuoso
posted Hide Post
Unlike Francis I like DATETRAN, and for full detail:
DEFINE FILE CAR
YR/A2='14';
MO/A2='01';
I_YM/I4YM = EDIT(YR | MO);
D_MY/MY = I_YM;
A_MY/A9 = DATETRAN(D_MY, '(MY)', '(t-)', 'EN', 6, 'A6');
END
TABLE FILE CAR
PRINT YR MO I_YM D_MY A_MY
BY COUNTRY NOPRINT
WHERE RECORDLIMIT EQ 1
END


Alan.
WF 7.705/8.007
 
Posts: 1451 | Location: Portugal | Registered: February 07, 2007Report This Post
Platinum Member
posted Hide Post
Thank you Francis and Shakila..
After posting this topic.. I decided to just use the DECODE.. as Francis suggested

I tried DATETRAN method also as it was posted here before.. but after going thru all the hoops, it returned 10-12, 11-12 etc

I think editing Mt month to Char. would be the smart way to go.. But IBI does think so..


Prod/Dev/Test: WF 8.1.5 on (Windows Server 2012 R2 )
SandBox: WebFocus Server 8.1.5 on Windows Server 2008 R2
WebFOCUS App Studio 8.1.5 and Developer Studio 8.1.5 on Windows 7
 
Posts: 134 | Location: USA | Registered: August 21, 2008Report This Post
Virtuoso
posted Hide Post
From a multi language pov, using DATETRAN is a better solution because you can pass the language as a parameter in the function and have the month in the proper description :
quote:
DTE /A20 = DATETRAN(DATECVT(YRMODY, 'I4YM','MY'), '(MY)', '(-t)', '&LANG.EVAL', 20, 'A20') ;


WF versions : Prod 8.2.04M gen 33, Dev 8.2.04M gen 33, OS : Windows, DB : MSSQL, Outputs : HTML, Excel, PDF
In Focus since 2007
 
Posts: 2409 | Location: Montreal Area, Qc, CA | Registered: September 25, 2013Report This Post
Platinum Member
posted Hide Post
Thanks Alan and MartinY

DATETRAN method worked.. but if the Mo and Yr are blank (no data), it comes up with Dec-00..

in the DECODE, I have it 'N/A'
MON1/A4= DECODE GL_MO ('01' 'Jan-' '02' 'Feb-' '03' 'Mar-' '04' 'Apr-' '05' 'May-' '06' 'Jun-' '07' 'Jul-' '08' 'Aug-' '09' 'Sep-' '10' 'Oct-' '11' 'Nov-' '12' 'Dec-' ELSE 'N/A');

PERIOD1/A6= MON1|EDIT(YR1,'$$99');
..

Thanks All


Prod/Dev/Test: WF 8.1.5 on (Windows Server 2012 R2 )
SandBox: WebFocus Server 8.1.5 on Windows Server 2008 R2
WebFOCUS App Studio 8.1.5 and Developer Studio 8.1.5 on Windows 7
 
Posts: 134 | Location: USA | Registered: August 21, 2008Report This Post
Virtuoso
posted Hide Post
DEFINE FILE CAR
YR/A2='14';
MO/A2=' ';
I_YM/I4YM = EDIT(YR | MO);
D_MY/MY = I_YM;
A_MY/A9 MISSING ON= IF DATETRAN(D_MY, '(MY)', '(t-)', 'EN', 6, 'A6') EQ 'Dec-00' THEN MISSING ELSE DATETRAN(D_MY, '(MY)', '(t-)', 'EN', 6, 'A6') ;

END
TABLE FILE CAR
PRINT YR MO I_YM D_MY A_MY
BY COUNTRY NOPRINT
WHERE RECORDLIMIT EQ 1
ON TABLE SET NODATA 'N/A'
END


Alan.
WF 7.705/8.007
 
Posts: 1451 | Location: Portugal | Registered: February 07, 2007Report This Post
Platinum Member
posted Hide Post
Thanks Alan.. It worked..


Prod/Dev/Test: WF 8.1.5 on (Windows Server 2012 R2 )
SandBox: WebFocus Server 8.1.5 on Windows Server 2008 R2
WebFOCUS App Studio 8.1.5 and Developer Studio 8.1.5 on Windows 7
 
Posts: 134 | Location: USA | Registered: August 21, 2008Report This Post
Platinum Member
posted Hide Post
Using FPRINT is another option:

Dialogue Manager

-SET &MT=03;
-SET &YR=14;
-SET &FDATE=IF &MT LT 1 OR &MT GT 12 THEN 'N/A' ELSE 
-           IF &YR LT 1 OR &YR GT 99 THEN 'N/A' ELSE EDIT(FPRINT(&MT.EVAL&YR.EVAL,'I4MTY','A6'),'999$-99');
-TYPE &FDATE


Focus Reporting Language

TABLE FILE CAR
SUM COUNTRY NOPRINT
COMPUTE MT/A2='02';
COMPUTE YR/A2='14';
COMPUTE FDATE/A6=IF EDIT(MT) LT 1 OR EDIT(MT) GT 12 THEN 'N/A' ELSE 
                 IF EDIT(YR) LT 1 OR EDIT(YR) GT 99 THEN 'N/A' ELSE EDIT(FPRINT(MT|YR,'A4MTY',FDATE),'999$-99');
END



In FOCUS since 1985 - WF 8.009/8.104 Win 8 Outputs: ALL of 'em! Adapters: Sql Server Teradata Oracle
 
Posts: 161 | Location: Dallas, TX | Registered: February 20, 2009Report 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     [SOLVED] Converting date to Jan-14 (Mtr)

Copyright © 1996-2020 Information Builders