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] Working Days - The hard way

Read-Only Read-Only Topic
Go
Search
Notify
Tools
[SOLVED] Working Days - The hard way
 Login/Join
 
Platinum Member
posted
I am hoping that there is at least one other person who has found themselves at a client who, for whatever reason, either cannot or will not create / update a holiday file. But they do still want date differences calculated using Working Days. So here is the situation:

TABLE_1 has a lot of data, and returns 2-3K rows with a normal report query. Within that table are two fields. START_DATE/YYMD and END_DATE/YYMD.

Now we all know that I can eliminate the weekends by using "WD" in the DATEDIF function, and if I had a holiday file "BD" would do the job. As I said at the start, I do not have, nor am I likely to get a holiday file. However, what I do have is:


TABLE_2, small table, has Company 'Holidays' in 2 fields DATE/YYMD and DESC/A50.

For the life of me I cannot figure out how, in a clean and simple manner I can then leave behind for the FTE's to understand, to determine how many rows in TABLE_2 are within the date range defined by the START_DATE and END_DATE in Table 1.

I tried to create a holiday file from TABLE_2 but security is locked down so tightly here I cannot save it where the SET HDAY command can find it. I have no access to any configuration files either.

WebFOCUS 7.6.10 on VMS

This message has been edited. Last edited by: rfbowley,


Robert F. Bowley Jr.
Owner
TaRa Solutions, LLC

In WebFOCUS since 2001
 
Posts: 132 | Location: Gadsden, Al | Registered: July 22, 2005Report This Post
Virtuoso
posted Hide Post
Have you tried something like this:

SET HOLDLIST = PRINTONLY
-*
TABLE FILE TABLE_2
BY DATE
ON TABLE SAVE AS HDAYTEMP
END
-RUN
-*
SET HDAY = TEMP


WebFOCUS 7.7.05
 
Posts: 1213 | Location: Seattle, Washington - USA | Registered: October 22, 2007Report This Post
Platinum Member
posted Hide Post
Unfortunately, yes.

  
SET HOLDLIST = PRINTONLY
 TABLE FILE TEMP
 PRINT GREGORIAN_DATE
 ON TABLE SAVE AS HDAYTEMP
 END
 -RUN
 0 NUMBER OF RECORDS IN TABLE=       18  LINES=     18
 ALPHANUMERIC RECORD NAMED  HDAYTEMP
 0 FIELDNAME                         ALIAS         FORMAT        LENGTH
 GREGORIAN_DATE                    E01           A8              8
 TOTAL                                                           8
 0 DCB USED WITH FILE HDAYTEMP IS DCB=(RECFM=FB,LRECL=00008,BLKSIZE=00160)
 -*
 SET HDAY = TEMP
 -EXIT
 (FOC1892) FILE NOT FOUND : HDAYTEMPERRORS  *



Robert F. Bowley Jr.
Owner
TaRa Solutions, LLC

In WebFOCUS since 2001
 
Posts: 132 | Location: Gadsden, Al | Registered: July 22, 2005Report This Post
Gold member
posted Hide Post
Suppose you did a conditional join between the two tables and then counted up the results and joined the counts back up to FILE_1?

Something like this perhaps:

JOIN LEFT_OUTER FILE FILE_1 AT START_DATE
  TO MULTIPLE FILE FILE_2 AT DATE
  WHERE DATE GE START_DATE;
  WHERE DATE LE END_DATE;
END
 


WebFocus 8201m on Windows; App Studio 8201; Procedures: WebFocus with SQL; HTML Parameter Pages & Dashboard; Output: Excel, HTML, & PDF.
 
Posts: 88 | Location: MI | Registered: July 23, 2009Report This Post
Virtuoso
posted Hide Post
If security permits, you might be able to use a public holidays file. Those are typically used from agenda applications, such as iCal and Outlock too. For example: http://www.mozilla.org/project...lendar/holidays.html

I'm assuming you have sufficient privileges to create focus databases and that your focus system user can download files off the internet.
You may need to generate a calendar file to match it against as well.


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 :
 
Posts: 1669 | Location: Enschede, Netherlands | Registered: August 12, 2010Report This Post
Platinum Member
posted Hide Post
Rob: Unfortunately this must be done on a record by record basis. The start and end dates will usually be different. So I could conceivably have 2K different date ranges in one report that I have to figure out how many holidays are in each range.

Wep. TABLE_2 has all the holidays, so creation of a holidays file is no problem. It is using it that is giving me the headache.


Robert F. Bowley Jr.
Owner
TaRa Solutions, LLC

In WebFOCUS since 2001
 
Posts: 132 | Location: Gadsden, Al | Registered: July 22, 2005Report This Post
Virtuoso
posted Hide Post
Robert,

I think this could help you.
I created, using the CAR file, a file with 2 dates, FRDATE, TODATE.
I also created a holiday file. You can probably do the same using the one on hand.
With these 2 I can calculate the number of holidays between FRDATE and TODATE which you can use to subtract from the WDays.
  
-* File rfbowley01.fex
DEFINE FILE CAR
FRDATE/YYMD=DCOST/100 + 100 * 366 - 50;
TODATE/YYMD=RCOST/100 + 100 * 366 - 50;
END
TABLE FILE CAR
PRINT  FRDATE TODATE
BY COUNTRY BY CAR BY MODEL BY BODY
ON TABLE HOLD AS RFB1 FORMAT FOCUS
END
-RUN
EX -LINES 6 EDAPUT MASTER,HLDAY,C,MEM
FILENAME=HLDAY, SUFFIX=FOC
 SEGNAME=LINK, SEGTYPE=S1
  FIELDNAME=BLANK, ALIAS=BLANK, FORMAT=A1, INDEX=I, $
 SEGNAME=HLDAY, SEGTYPE=S1, PARENT=LINK
  FIELDNAME=HLDATE, ALIAS=HLDATE, FORMAT=YYMD, $
-RUN
CREATE FILE HLDAY
-RUN
MODIFY FILE HLDAY
FREEFORM HLDATE
DATA
-REPEAT #L FOR &I FROM 1 TO 12;
-SET &J=IF &I GE 10 THEN &I ELSE '0'|&I; 
HLDATE='2001/&I/01',$
-#L
END
-RUN
JOIN BLANK WITH FRDATE IN RFB1 TO BLANK IN HLDAY AS A_
DEFINE FILE RFB1
BLANK/A1 WITH FRDATE=' ';
HLDAYCOUNT/I3=IF HLDATE GE FRDATE AND HLDATE LE TODATE THEN 1 ELSE 0;
END
TABLE FILE RFB1
SUM 
  FRDATE TODATE HLDAYCOUNT
BY COUNTRY BY CAR BY MODEL BY BODY
END

See if this helps.


Daniel
In Focus since 1982
wf 8.202M/Win10/IIS/SSA - WrapApp Front End for WF

 
Posts: 1980 | Location: Tel Aviv, Israel | Registered: March 23, 2006Report This Post
Gold member
posted Hide Post
I'm not sure if I'm misunderstanding, or if I'm not communicating well. . .

If we tabled the results after the conditional join, it would look something like this:


TABLE FILE FILE_1
PRINT 
   EMPLOYEE_ID START_DATE END_DATE DATE DESC
ON TABLE HOLD AS HOLIDAYS
END

     EMP_ID  START_DATE  END_DATE    DATE        DESC
     ------  ----------  --------    ----        ---- 
      12345  2008/07/19  2009/07/01  2009/01/01  New Years                     
      12345  2008/07/19  2009/07/01  2009/04/10  Good Friday                   
      12345  2008/07/19  2009/07/01  2009/05/25  Memorial Day                  
      22345  2009/07/01  2010/07/01  2009/07/03  Independence Day              
      22345  2009/07/01  2010/07/01  2009/09/07  Labor Day                     
      22345  2009/07/01  2010/07/01  2009/11/26  Thanksgiving                  
      22345  2009/07/01  2010/07/01  2009/12/25  Christmas                     
      22345  2009/07/01  2010/07/01  2010/01/01  New Years                     
      22345  2009/07/01  2010/07/01  2010/04/02  Good Friday                   
      22345  2009/07/01  2010/07/01  2010/05/31  Memorial Day                  


And then we could proceed like this:

TABLE FILE HOLIDAYS
SUM CNT.DATE AS 'HOLIDAY_CNT'
BY EMPLOYEE_ID
BY START_DATE
WHERE DATE NE ''
ON TABLE PCHOLD FORMAT WP
END


To get these results:

                           
  EMPLOYEE_ID  START_DATE  HOLIDAY_CNT
  -----------  ----------  -----------
        12345  2008/07/19            4
        22345  2009/07/01            7


WebFocus 8201m on Windows; App Studio 8201; Procedures: WebFocus with SQL; HTML Parameter Pages & Dashboard; Output: Excel, HTML, & PDF.
 
Posts: 88 | Location: MI | Registered: July 23, 2009Report This Post
Master
posted Hide Post
It looks like you are on z/OS.

If so, it should be possible to create a application specific/departmental version of the Holiday File, and make it available to your programs. (Given what you are saying it sounds like you do not have the the option to create a site-wide Holiday File.)

Here is something you can try, however I am not able to test it because I am no longer on z/OS.

Create a new ERRORS partitioned dataset (PDS), e.g. 'DEPT.ERRORS.DATA'. (I'd use the same DCB as the production version.)

Add a member to the new PDS for your Holiday File, e.g., (HDAY2012).

Open the member in SPF Edit and type in your holdiays, e.g., 20120101 New Years Day.

In your application concatenate your departmental ERRORS file to the production version.

You could do this in the JCL or via DYANAM.

Something like:

DYNAM ALLOC FILE ERRORS DS DEPT.ERRORS.DATA SHR REUSE 
DYNAM ALLOC FILE ERRORS1 DS PROD.ERRORS.DATA SHR REUSE 
DYNAM CONCAT DDN ERRORS ERRORS1


Your code should work like normal, reading from the normal PROD version of the ERRORS file when/as needed. When the code sees the SET HDAY = 2012 command, it should pull from the departmental version of the ERROR file.




Pilot: WebFOCUS 8.2.06 Test: WebFOCUS 8.1.05M Prod: WebFOCUS 8.1.05M Server: Windows Server 2016/Tomcat Standalone Workstation: Windows 10/IE11+Edge Database: Oracle 12c, Netezza, & MS SQL Server 2019 Output: AHTML/XLSX/HTML/PDF/JSCHART Tools: WFDS, Repository Content, BI Portal Designer & ReportCaster
 
Posts: 822 | Registered: April 23, 2003Report This Post
Platinum Member
posted Hide Post
MathematicalRob, you are indeed a lifesaver. Here is the Final result that works perfectly Big Grin

 
-**********************************************************************************************
-*	Get the Contents of the Holiday table on DB2 into a FOCUS table
-**********************************************************************************************

SQL DB2
SELECT * FROM FIWP.TBHOLIDAY
WHERE BCBS_FL = 'Y'
;

TABLE
ON TABLE HOLD
END
-RUN
TABLE FILE HOLD
PRINT HOLIDAY_DESC
BY HOLIDAY_DT
ON TABLE HOLD AS HDAYS FORMAT FOCUS INDEX HOLIDAY_DT
END
-RUN

-**********************************************************************************************
-*	Get the DATE values from H1 into a FOCUS table
-**********************************************************************************************

TABLE FILE H1
BY DATE
ON TABLE HOLD AS EFFDATES FORMAT FOCUS INDEX DATE
END
-RUN

JOIN LEFT_OUTER FILE EFFDATES AT DATE TAG T1 TO ALL
  				FILE HDAYS AT HOLIDAY_DT TAG T2 AS JW1
  WHERE HDAYS.HOLIDAY_DT GE EFFDATES.DATE;
  WHERE HDAYS.HOLIDAY_DT LE &YYMD.EVAL;
END

-**********************************************************************************************
-*	Count the hoildays and hold the results in a FOCUS table
-**********************************************************************************************
TABLE FILE EFFDATES
SUM CNT.HOLIDAY_DT AS 'HOLCNT'
BY DATE
ON TABLE HOLD AS HOLCOUNT FORMAT FOCUS INDEX DATE
END
-RUN

JOIN LEFT_OUTER DATE IN H1
	TO UNIQUE DATE IN HOLCOUNT
	AS J0
END

 


Robert F. Bowley Jr.
Owner
TaRa Solutions, LLC

In WebFOCUS since 2001
 
Posts: 132 | Location: Gadsden, Al | Registered: July 22, 2005Report This Post
Gold member
posted Hide Post
Great; I'm glad it worked out!

Rob


WebFocus 8201m on Windows; App Studio 8201; Procedures: WebFocus with SQL; HTML Parameter Pages & Dashboard; Output: Excel, HTML, & PDF.
 
Posts: 88 | Location: MI | Registered: July 23, 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] Working Days - The hard way

Copyright © 1996-2020 Information Builders