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     Sorting Date chronologically
Go
New
Search
Notify
Tools
Reply
  
Sorting Date chronologically
 Login/Join
 
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, 2013Reply With QuoteReport This Post
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, 2003Reply With QuoteReport This Post
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, 2005Reply With QuoteReport This Post
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, 2010Reply With QuoteReport This Post
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, 2005Reply With QuoteReport This Post
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: 6319 | Location: 33°49'23.0"S, 151°11'41.0"E | Registered: October 31, 2006Reply With QuoteReport This Post
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, 2007Reply With QuoteReport This Post
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, 2013Reply With QuoteReport This Post
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, 2013Reply With QuoteReport This Post
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, 2013Reply With QuoteReport This Post
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, 2003Reply With QuoteReport This Post
  Powered by Social Strata  
 

Focal Point    Focal Point Forums  Hop To Forum Categories  WebFOCUS/FOCUS Forum on Focal Point     Sorting Date chronologically

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