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.
Each month? Won't you need to specify at least a year as well then? Or are you simply interested in the week-number at the first of the month for a given date?
You can get the week that the first of each month is in based on WEEKFIRST at Sunday (check the docs there). With a (list of such) date(s), you could then use HYYWD to determine the week-number for each date. It includes the year in the string (important around new-years day!), but you could simply strip that off.
That's all a bit of a hassle though. IMHO, having a calendar table that contains the necessary information (at least week + weekyear) in an RDBMS is more convenient to use, and not limited to this particular application. It also allows you to add things like fiscal year, holidays, etc.
WebFOCUS 8.1.03, Windows 7-64/2008-64, IBM DB2/400, Oracle 11g & RDB, MS SQL-Server 2005, SAP, PostgreSQL 11, Output: HTML, PDF, Excel 2010 : Member of User Group Benelux :
I have this report that looks like a grid (or excel spreadsheet) that is sorted by Across Month, then Across each Sunday in the week. I need to put a bold line separating the months.
Jan | Feb | Mar 06 13 20 27 | 03 10 17 24 | 03 10 17 24 31 | | | |
Below is my code:
SET ASNAMES = ON SET HOLDATTR = ON SET NODATA=' ' -*SET SQUEEZE=ON -*SET SPACES = 0
DEFINE FILE DW_CODE_B_V2 VALID_YEAR/YYMD = VALID_UNTIL; VALID_YR/YY = VALID_YEAR; DISPO_YR/YYMD = DISPOSITION_DATE; DISPO_YEAR/YY = IF DISPO_YR EQ '' THEN '9999' ELSE DISPO_YR; END
TABLE FILE DW_CODE_B_V2 SUM DW_CODE_B_V2.DW_CODE_B_V2.CODE_B BY DIVISION BY CODE_B BY COST_CENTER_DESC WHERE COST_CENTER_DESC NOT LIKE '%DO NOT USE%' OR 'RG331' OR 'RG318' OR 'RG311' OR 'RG306' OR 'J10' OR 'J11' OR 'J8' OR 'RW12' OR 'LR502' OR 'LRG14' OR 'CN-KBC750' OR 'CRG01' OR 'AS%' OR 'KM%' OR 'LRT2' OR 'RG310' OR 'RG319' OR 'RGS11' OR 'RV10%' OR 'RV20' OR 'SAR-RGI7' OR 'SBC34' OR 'SBC4' OR 'TLU01' OR 'UC2400'; WHERE BUSINESS_SEGMENT EQ &BUSINESS_SEGMENT; WHERE SUB_BUSINESS_SEGMENT EQ &SUB_BUSINESS_SEGMENT; WHERE DIVISION EQ &DIVISION; WHERE SUB_DIVISION EQ &SUB_DIVISION; WHERE COST_CENTER_GROUP EQ &COST_CENTER_GROUP; WHERE COST_CENTER EQ &CODE_B; WHERE VALID_YEAR GE &CURYEAR; WHERE DISPO_YEAR GE &CURYEAR; ON TABLE SET PAGE-NUM NOLEAD ON TABLE NOTOTAL ON TABLE HOLD AS CODEBDAT END
JOIN LEFT_OUTER CODEBDAT.CODEBDAT.CODE_B IN CODEBDAT TO MULTIPLE CODE_B_DATES.CODE_B_DATES.CODE_B IN code_b_dates TAG J0 AS J0 END
TABLE FILE CODEBDAT BY J0.CODE_B_DATES.WEEK_END_DATE -* J0.CODE_B_DATES.WEEK_BEG_DATE BY J0.CODE_B_DATES.DIVISION BY J0.CODE_B_DATES.CODE_B BY J0.CODE_B_DATES.COST_CENTER_DESC BY J0.CODE_B_DATES.YEAR_PERIOD_DAY WHERE ACCOUNT_YEAR EQ &CURYEAR; ON TABLE SET PAGE-NUM NOLEAD ON TABLE NOTOTAL ON TABLE HOLD AS WKNDDAYS END
JOIN LEFT_OUTER CODEBDAT.CODEBDAT.CODE_B IN CODEBDAT TO MULTIPLE DW_BUDGET_DAILY_SHIFTS.BUDGETDAILYSHIFTS.CODE_B IN dw_budget_daily_shifts TAG J2 AS J2 END
TABLE FILE CODEBDAT BY IFS_CUSTOMER_ID -*BY WEEK_BEG BY DIVISION BY CODE_B BY WEEK_END WHERE ACCOUNT_YEAR EQ &CURYEAR; WHERE BUDGET_TYPE EQ '&BUDGET_TYPE'; ON TABLE SET PAGE-NUM NOLEAD ON TABLE NOTOTAL ON TABLE HOLD AS WEEKENDS END
JOIN LEFT_OUTER WKNDDAYS.WKNDDAYS.CODE_B AND WKNDDAYS.WKNDDAYS.WEEK_END_DATE IN WKNDDAYS TO UNIQUE WEEKENDS.WEEKENDS.CODE_B AND WEEKENDS.WEEKENDS.WEEK_END IN WEEKENDS TAG J1 AS J1 END
DEFINE FILE WKNDDAYS WEEK_ENDING/YYMD = WKNDDAYS.WKNDDAYS.WEEK_END; END
TABLE FILE WKNDDAYS SUM J1.WEEKENDS.IFS_CUSTOMER_ID BY DIVISION BY CODE_B BY COST_CENTER_DESC BY WEEK_ENDING -*BY WKNDDAYS.WKNDDAYS.WEEK_END -*BY WEEK_BEG_DATE -*WHERE BUDGET_TYPE EQ '&BUDGET_TYPE'; ON TABLE SET PAGE-NUM NOLEAD ON TABLE NOTOTAL ON TABLE HOLD AS TEMPDATA END
DEFINE FILE TEMPDATA WK_BEG_DATE/YYMD = DATEADD(WEEK_END,'D',-6); YR/YY=WK_BEG_DATE; YEAR/A4=EDIT(YR); MTH/M=WK_BEG_DATE; MONTH/I2=EDIT(MTH); DAY/D=WK_BEG_DATE; DAY1/A2=EDIT(DAY); MONTHDAY/A7=MONTH |'/'| DAY1; MONTH_NAME/A12 = DECODE MTH ('01' JAN '02' FEB '03' MAR '04' APR '05' MAY '06' JUN '07' JUL '08' AUG '09' SEP '10' OCT '11' NOV '12' DEC ); SPACES/A30=' '; COST_CENTER/A250V=IF COST_CENTER_DESC LIKE 'RV10 and Truck%' THEN 'RV10/TruckVac' ELSE COST_CENTER_DESC; COSTCENTER/A255 = COST_CENTER | '&SPCS'; DUMMY/A5='DUMMY'; CURRENT_WEEK/YYMD=&CURRENT_WEEK; END
I know the topic is marked solved, but it seemed like an interesting problem to demonstrate using DECODE files to map input dates to arbitrary data, such as the previous Sunday week.
-* -------------------------------------------------------------------
-* Example: creating a DECODE file for fast date lookup into arbitrary data
-* -------------------------------------------------------------------
-*
-* The idea here is to create a Focus Decode file that can be used to
-* lookup information for particular dates, such as week month and day, week
-* number, holiday or business day, etc. Here is an example of some code/decode
-* entries in the decode file for week month and day, where the input date
-* is mapped to the *previous* Sunday, whose year, month, and day
-* represents the week used for the date:
-*
-* 20190501,2019040428
-* 20190502,2019040428
-* 20190503,2019040428
-* 20190504,2019040428
-* 20190505,2019050505
-* 20190506,2019050505
-* 20190507,2019050505
-* 20190508,2019050505
-* 20190509,2019050505
-* 20190510,2019050505
-* 20190511,2019050505
-* 20190512,2019050512
-* 20190513,2019050512
-* 20190514,2019050512
-* 20190515,2019050512
-*
-* We see that for May 1, 2019, the previous Sunday was April 28, 2019, etc.
-* With this decode file, we can use a DECODE Focus subcommand to
-* decode an I8YYMD olddate to find the WEEKSUN A8 value: e.g.
-*
-* WEEKSUN/A8 = DECODE INDAY (MYDCOD ELSE 'UNKNOWN ');
-*
-* From the WEEKSUN field, we can easily get the start week year, month, or
-* day using the EDIT subcommand: e.g.
-*
-* WEEKMON/A2 = EDIT(WEEKSUN,'$$$$$$99');
-* -------------------------------------------------------------------
-* First, we create a very simple Focus file that only contains a key
-* (ORDERKEY) that is simply an ascending integer starting from 1,
-* along with random IVAL and AVAL fields just for fun. Take the following
-* master and put it into MYSMALL.MAS:
-GOTO HERE
SUFFIX=FOC
SEGNAME=ONLY,SEGTYPE=S1,$
FIELD=ORDERKEY,,I6,$
FIELD=IVAL,,I2,$
FIELD=AVAL,,A2,$
END
-HERE
-* Now create the MYSMALL file. Here, we populate the file with 20,000
-* entries going from 1 to 20,000:
CREATE FILE MYSMALL
-RUN
MODIFY FILE MYSMALL
COMPUTE
A1DUM/A1 = ;
FIXFORM 20000(A1DUM/1 X-1)
COMPUTE
ORDERKEY = ORDERKEY + 1;
SEED = 1;
IVAL = PRDUNI(SEED,'D2') * 100;
AVAL = EDIT(IVAL) ;
MATCH ORDERKEY
ON MATCH REJECT
ON NOMATCH INCLUDE
LOG DUPL MSG OFF
DATA
1
END
-RUN
-* Now that we have the MYSMALL file, we will use it to create our DECODE file
-* as described above. In the example just below, we start with the date
-* Jan 1, 2019, and create the MYDCOD DECODE file for 200 days after the
-* start date:
DEFINE FILE MYSMALL
TODAY/I8YYMD = 20190101 ;
TOD/YYMD = TODAY ;
CURRDAY/YYMD = TOD + ORDERKEY - 1;
ICURR/I8YYMD = CURRDAY ;
MYDAY/I6 = CURRDAY - (INT(CURRDAY/7)*7) ;
MYDAYB/I6 = IF MYDAY EQ 6 THEN 0 ELSE MYDAY + 1;
MYCSUN/YYMD = IF(MYDAYB NE 0)THEN CURRDAY - MYDAYB ELSE CURRDAY;
IMYCSUN/I8YYMD = MYCSUN ;
AMYC/A8 = EDIT(IMYCSUN) ;
DCOD/A10 = EDIT(AMYC,'999999') | EDIT(AMYC,'$$$$99') | EDIT(AMYC,'$$$$$$99');
LINE/A19 = EDIT(ICURR) | ',' | DCOD ;
END
-RUN
TABLE FILE MYSMALL
PRINT LINE
ON TABLE SAVE AS MYDCOD
IF RECORDLIMIT EQ 200
END
-RUN
-* And that's it! There is now a DECODE file with the name MYDCOD
-* that can be used by *any* program to quickly find the prevous
-* Sunday week date based on an input I8YYMD date. In the example
-* below, we re-use the MYSMALL file we just created only to provide
-* some input data where we can call DECODE with our new MYDCoD file.
-* For the MYSMALL Focus file, we associate ORDERKEY with consecutive
-* days starting with Jan 1, 2019, (e.g. oRDERKEY=1 turns into
-* I8YYMD 20190101), and use IVAL as the fake data for each day.
SET PANEL=80
DEFINE FILE MYSMALL
TODAY/I8YYMD = 20190101 ;
TOD/YYMD = TODAY ;
CURRDAY/YYMD = TOD + ORDERKEY - 1;
ICURR/I8YYMD = CURRDAY ;
MYDCOD/A10 = DECODE ICURR (MYDCOD ELSE 'UNKNOWN');
MYD/D3 = IVAL ;
ISYMON/A6 = EDIT(MYDCOD,'999999') ;
ISMON/A2 = EDIT(MYDCOD,'$$$$$$99') ;
ISDAY/A2 = EDIT(MYDCOD,'$$$$$$$$99') ;
END
-RUN
TABLE FILE MYSMALL
SUM MYD ACROSS ISYMON AS '' ACROSS ISMON AS '' ACROSS ISDAY AS ''
IF RECORDLIMIT EQ 200
END
-RUN
-EXIT
PAGE 1.1
201812 201901 201902 201903
12 01 02 03
30 06 13 20 27 03 10 17 24 03 10
------------------------------------------------------------------------------
276 327 303 459 389 358 355 308 284 229 328 2
PAGE 1.2
201904 201905 201
04 05 06
17 24 31 07 14 21 28 05 12 19 26 02
-------------------------------------------------------------------------------
96 256 396 302 316 355 413 285 250 243 243 326
PAGE 1.3
906 201907
07
09 16 23 30 07 14
----------------------------------------------
300 564 225 197 331 171