In this section: Reference: |
In some data sources, date values are stored in alphanumeric format without any particular standard, with any combination of components, such as year, quarter, and month, and with any delimiter. In a sorted report, if such data is sorted alphabetically, the sequence does not make business sense. To ensure adequate sorting, aggregation, and reporting on date fields, FOCUS can convert the alphanumeric dates into standard FOCUS date format using a conversion pattern that you can specify in the Master File attribute called DATEPATTERN.
Each element in the pattern is either a constant character which must appear in the actual input or a variable that represents a date component. You must edit the USAGE attribute in the Master File so that it accounts for the date elements in the date pattern. The maximum length of the DATEPATTERN string is 64.
How to: |
The valid date components (variables) are year, quarter, month, day, and day of week. In the date pattern, variables are enclosed in square brackets (these brackets are not part of the input or output. Note that if the data contains brackets, you must use an escape character in the date pattern to distinguish the brackets in the data from the brackets used for enclosing variables).
Specifies a four-digit year.
Specifies a four-digit year.
Specifies a two-digit year.
Specifies a zero-suppressed two-digit year (for example, 8 for 2008).
Specifies a blank-padded two-digit year.
Specifies a two-digit month number.
Specifies a zero-suppressed month number.
Specifies a blank-padded month number.
Specifies a three-character month name in upper case.
Specifies a three-character month name in lower case.
Specifies a three-character month name in mixed case.
Specifies a full month name in upper case.
Specifies a full month name in lower case.
Specifies a full month name in mixed case.
Specifies a two-digit day of the month.
Specifies a zero-suppressed day of the month.
Specifies a blank-padded day of the month.
Specifies a three-digit day of the year.
Specifies a zero-suppressed day of the year.
Specifies a blank-padded day of the year.
Specifies a one-digit day of the week.
Specifies a three-character day name in upper case.
Specifies a three-character day name in lower case.
Specifies a three-character day name in mixed case.
Specifies a full day name in upper case.
Specifies a full day name in lower case.
Specifies a full day name in mixed case.
For the day of the week, the WEEKFIRST setting defines which day is day 1.
Between the variables, you can insert any constant values.
If you want to insert a character that would normally be interpreted as part of a variable, use the backslash character as an escape character. For example:
For a single quotation mark, use two consecutive single quotation marks ('').
If the date in the data source is of the form CY 2001 Q1, the DATEPATTERN attribute is:
DATEPATTERN = 'CY [YYYY] Q[Q]'
If the date in the data source is of the form Jan 31, 01, the DATEPATTERN attribute is:
DATEPATTERN = '[Mon] [DD], [YY]'
If the date in the data source is of the form APR-06, the DATEPATTERN attribute is:
DATEPATTERN = '[MON]-[YY]'
If the date in the data source is of the form APR - 06, the DATEPATTERN attribute is:
DATEPATTERN = '[MON] - [YY]'
If the date in the data source is of the form APR '06, the DATEPATTERN attribute is:
DATEPATTERN = '[MON] ''[YY]'
If the date in the data source is of the form APR [06], the DATEPATTERN attribute is:
DATEPATTERN = '[MON] \[[YY]\]' (or '[MON] \[[YY]]'
Note that the right square bracket does not have to be escaped.
In the following example, DATE1 is a sequential file containing the following data:
June 1, '02 June 2, '02 June 3, '02 June 10, '02 June 11, '02 June 12, '02 June 20, '02 June 21, '02 June 22, '02 June 1, '03 June 2, '03 June 3, '03 June 10, '03 June 11, '03 June 12, '03 June 20, '03 June 21, '03 June 22, '03 June 1, '04 June 2, '04 June 3, '04 June 4, '04 June 10, '04 June 11, '04 June 12, '04 June 20, '04 June 21, '04 June 22, '04
In the DATE1 Master File, the DATE1 field has alphanumeric USAGE and ACTUAL formats, each A18:
FILENAME=DATE1 , SUFFIX=FIX ,
DATASET = 'USER1.DATE1.FOCTEMP' , $
SEGMENT=FILE1, SEGTYPE=S0, $
FIELDNAME=DATE1, ALIAS=E01, USAGE=A18, ACTUAL=A18, $
The following request sorts by the DATE1 FIELD:
TABLE FILE DATE1 PRINT DATE1 NOPRINT BY DATE1 ON TABLE SET PAGE NOPAGE END
The output shows that the alphanumeric dates are sorted alphabetically, not chronologically:
DATE1 ----- June 1, '02 June 1, '03 June 1, '04 June 10, '02 June 10, '03 June 10, '04 June 11, '02 June 11, '03 June 11, '04 June 12, '02 June 12, '03 June 12, '04 June 2, '02 June 2, '03 June 2, '04 June 20, '02 June 20, '03 June 20, '04 June 21, '02 June 21, '03 June 21, '04 June 22, '02 June 22, '03 June 22, '04 June 3, '02 June 3, '03 June 3, '04 June 4, '04
In order to sort the data correctly, you can add a DATEPATTERN attribute to the Master File that enablesFOCUS to convert the date to a FOCUS date field. You must also edit the USAGE format to make it a FOCUS date format. To construct the appropriate pattern, you must account for all of the components in the stored date. The alphanumeric date has the following variables and constants:
The edited Master File follows. Note the addition of the DEFCENT attribute to convert the two-digit year to a four-digit year:
FILENAME=DATE1 , SUFFIX=FIX ,
DATASET = 'USER1.DATE1.FOCTEM'P , $
SEGMENT=FILE1, SEGTYPE=S0, $
FIELDNAME=DATE1, ALIAS=E01, USAGE=A18, ACTUAL=A18,
DEFCENT=20,
DATEPATTERN = '[Month] [dd], ''[YY]', $
Now, issuing the same request produces the following output. Note that DATE1 has been converted to a FOCUS date in MtrDYY format (as specified in the USAGE format):
DATE1 ----- June 1, 2002 June 2, 2002 June 3, 2002 June 10, 2002 June 11, 2002 June 12, 2002 June 20, 2002 June 21, 2002 June 22, 2002 June 1, 2003 June 2, 2003 June 3, 2003 June 10, 2003 June 11, 2003 June 12, 2003 June 20, 2003 June 21, 2003 June 22, 2003 June 1, 2004 June 2, 2004 June 3, 2004 June 4, 2004 June 10, 2004 June 11, 2004 June 12, 2004 June 20, 2004 June 21, 2004 June 22, 2004
Information Builders |