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     Sorting Date chronologically

Read-Only Read-Only Topic
Go
Search
Notify
Tools
Sorting Date chronologically
 Login/Join
 
Rejoice
Member
posted
Hi All,

I am new user of WF version 8. How do I sort chronologically a date that is defined varchar(20) as MM/DD/YY? Below is a sample of my code. Please advise


TABLE FILE RULE_USAGE
SUM allowed/I9C AS 'ALLOWED'
denied/I9C AS 'DENIED'
review/I9C AS 'REVIEW'
total_requests/I9C AS 'TOTAL'
BY HIGHEST service_day AS 'DATE' <-- service_day is defined as varchar [20) in MM/DD/YY format


Webfocus 8
Windows, All Outputs
 
Posts: 22 | Registered: May 07, 2013Report This Post
jimster06
Guru
posted Hide Post
In order to arrange the data by highest date, you will have to be able to access each portion of the MM/DD/YY date string, ie year, month, and day.
There are different ways of doing this but I would suggest that you consider using the DEFINE capability of WF. With this tool, you can separate out the elements of the date string and combine them into YYMMDD format, for example.
Be wary of cross-century dates, ie for events prior to Y2K because they will sort out first in the format you have. Your data may or may not have them.
WF date manipulation is well discussed in the book (almost) 1001 Ways to Work with Dates in WebFOCUS which, if you will be working in WF for any length of time, you ought to consider purchasing.


jimster06
DevStu WF 7.6.11
W7
HTML, PDF, EXL2K
 
Posts: 252 | Location: USA | Registered: April 15, 2003Report This Post
j.gross
Virtuoso
posted Hide Post
You can avoid manipulation of the mm dd yy components, and let the software do the conversion from text to smart date.

First transform it into an A6MDY legacy date field, then to a "smart date" format, using "default century and initial year" clauses to determine the century digits.
DEFINE FILE RULE_USAGE
  DATE2/A20=service_day ;
  DATE3/A6MDY=EDIT(DATE2,'99$99$99');
  DATE/MDY DFC 19 YRT 80 = DATE3;
END

TABLE FILE RULE_USAGE
 SUM ...
 BY HIGHEST DATE
...
END
 
Posts: 1925 | Location: NYC | In FOCUS since 1983 | Registered: January 11, 2005Report This Post
George Patton
Master
posted Hide Post
Both the above are good suggestions. What baffles me, is why anyone who took even an introductory computer programming course, would choose anything other than YYMD as a date format. Even if that's an A8 field it will still sort correctly. But as Professor Gross points out, once it is a Smart Date (stored as the number of seconds since Jan 1, 1900 I think) the display is entirely flexible. Ah well ....


WebFOCUS 7.7.05 Windows, Linux, DB2, IBM Lotus Notes, Firebird, Lotus Symphony/OpenOffice. Outputs PDF, Excel 2007 (for OpenOffice integration), WP
 
Posts: 674 | Location: Guelph, Ontario, Canada ... In Focus since 1985 | Registered: September 28, 2010Report This Post
j.gross
Virtuoso
posted Hide Post
One more note. If service_date is stored in a database in the format you described, and you need to *aggregate* data by service_date, it is likely that the aggregation by defined DATE would not be passed through by WebFOCUS in the generated SQL. If so it would be more efficient to pull the data grouped and sorted by service_date in its original format (so that WF passes through the aggregation), Hold the result, and define the date transformation against the Hold file.


- Jack Gross
WF through 8.1.05
 
Posts: 1925 | Location: NYC | In FOCUS since 1983 | Registered: January 11, 2005Report This Post
Waz
Expert
posted Hide Post
There is also another option.

Change the Usage A20 in the master file to MDYY.

Ther is a good chance that the date will be retrieved as a smartdate.

Master files can do amazing things!


Waz...

Prod:WebFOCUS 7.6.10/8.1.04Upgrade:WebFOCUS 8.2.07OS:LinuxOutputs:HTML, PDF, Excel, PPT
In Focus since 1984
Pity the lost knowledge of an old programmer!

 
Posts: 6347 | Location: 33°49'23.0"S, 151°11'41.0"E | Registered: October 31, 2006Report This Post
Alan B
Virtuoso
posted Hide Post
To convert an A20 MM/DD/YY into a YYMD smart date field in the MFD use DATEPATTERN.

FIELD=service_day,ALIAS=service_day,USAGE=YYMD,ACTUAL=A20V,
DATEPATTERN='[MM]/[DD]/[YY]',$

You could also have a USAGE of MDYY if you really prefer...

Now you have a field that can be used for sorting, however you can apply DEFCENT and YRTHRESH at a global or field level, FDEFCENT and FYRTHRESH could also be used.

See the describing data manual for full details.


Alan.
WF 7.705/8.007
 
Posts: 1451 | Location: Portugal | Registered: February 07, 2007Report This Post
Rejoice
Member
posted Hide Post
Thank you all for good suggestions.

I tried this suggestion and it worked.

DEFINE FILE RULE_USAGE
DATE2/A20=service_day ;
DATE3/A6MDY=EDIT(DATE2,'99$99$99');
DATE/MDY DFC 19 YRT 80 = DATE3;
END

TABLE FILE RULE_USAGE
SUM ...
BY HIGHEST DATE
...
END

Regarding the master file, I am using a common include sql module where date usage is CAST from datestamp to varchar 'MM/DD/YY'. Per your recommendation I can manipulate the dates in the SQL itself but I am avoiding impacts to other procedures calling this include module. Eventually, will go to this direction.


Webfocus 8
Windows, All Outputs
 
Posts: 22 | Registered: May 07, 2013Report This Post
Rejoice
Member
posted Hide Post
I have to make a correction to the code as EXL07 format does not take it properly ( unreadable content when opening the file). The correction is as follow:

DEFINE FILE RULE_USAGE
DATE2/A20=service_day ;
DATE3/A6MDY=EDIT(DATE2,'99$99$99');
DATE/MDY DFC 19 YRT 80 = DATE3;
END

TABLE FILE RULE_USAGE
SUM ...
BY HIGHEST DATE NOPRINT
BY HIGHEST DATE SERVICE_DAY
...
END


ON DATE SUBTOTAL AS 'Total for '
ON TABLE COLUMN-TOTAL AS 'TOTAL'


Webfocus 8
Windows, All Outputs
 
Posts: 22 | Registered: May 07, 2013Report This Post
Rejoice
Member
posted Hide Post
I just noticed when using AHTML format SUBTOTAL doesn't work. Any idea?


Webfocus 8
Windows, All Outputs
 
Posts: 22 | Registered: May 07, 2013Report This Post
Vivian
Platinum Member
posted Hide Post
Rejoice,

The book (almost) 1001 Ways to Work With Dates in WebFOCUS through Release 8.0.2 is available through www.aviter.com. It really does contain a wealth of information about date manipulations.

Vivian


Vivian Perlmutter
Aviter, Inc.


WebFOCUS Keysheet Rel. 8.0.2
(Almost) 1001 Ways to Work with Dates thru Rel. 8.0.2
Focus since 1982
WebFOCUS since the beginning
Vivian@aviter.com

 
Posts: 191 | Location: Henderson, Nevada | Registered: April 29, 2003Report 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     Sorting Date chronologically

Copyright © 1996-2020 Information Builders