Focal Point
Date manipulation (date +3 Business days)

This topic can be found at:
https://forums.informationbuilders.com/eve/forums/a/tpc/f/7971057331/m/9491013092

July 03, 2008, 11:34 AM
Luminita
Date manipulation (date +3 Business days)
Hi,

I've been trying to follow the instructions on this site to create a holiday file, but I can't seem to get it to work properly. The main difference is that we have a table that contains the holidays and will get updated as the dates change, so I'm trying to make use of that. Here is my code:

SET PAGE-NUM = OFF
SET BUSDAYS = _MTWTF_
-*
-SET &ECHO=ALL;
ENGINE SQLSYB SET DEFAULT_CONNECTION SYBX
SQL SQLSYB PREPARE SQLHLDYDATES FOR
select hldy_date 'HOLIDAY_DATE'
from reference..holiday
where typ = 'BANK' and grp_name = '#FED'
END
-*
APP FI HDAYSWAP DISK hdayswap.err
-*
TABLE FILE SQLHLDYDATES
PRINT HOLIDAY_DATE AS ''
BY HOLIDAY_DATE NOPRINT
ON TABLE HOLD AS HDAYSWAP FORMAT ALPHA
END
-RUN
-*
SET HDAY = swap
-*
DEFINE FILE CAR
TEMP_DATE/A14 = EDIT('05/22/2008', '$$$$$$9999') || EDIT('05/22/2008', '99') || EDIT('05/22/2008', '$$$99') ||('000000');
DAT2/HYYMDs = HINPUT(14, TEMP_DATE, 8, 'HYYMDs');
DAT3/MDYY = HDATE(DAT2, 'MDYY');
NEW_SETTLE_DATE/MDYY = DATEADD(DAT3, 'BD', 3);
END
TABLE FILE CAR
PRINT CAR NEW_SETTLE_DATE TEMP_DATE DAT2 DAT3
END
-RUN

When I run this I get (FOC1892) FILE NOT FOUND : HDAYswapERRORS *
I also tried
APP FI HDAYSWAP DISK wfs\bin\hdayswap.err
No luck, same error...

Has anyone created a holiday file this was, is it even possible?

Thank you for your help.
Luminita


7.6.6 on AIX platform
Output: variety (Excel, HTML and PDF)
July 03, 2008, 11:41 AM
Prarie
I know there has been an 8 character limit on hold files..but don't know which release it changed..I'd try changing that.


In Focus since 1993. WebFOCUS 7.7.03 Win 2003
Nope, that didn't help...


7.6.6 on AIX platform
Output: variety (Excel, HTML and PDF)
The holiday file MUST be located in a specific directory for this to work. This is not documented, but Frank Dutch helped recently:

Holiday File Location

On my server, this worked:

FILEDEF HDAYTEST DISK C:/ibi/srv76/wfs/bin/HDAYTEST.ERR

or

APP FI HDAYSWAP DISK C:/ibi/srv76/wfs/bin/hdayswap.err



Francis


Give me code, or give me retirement. In FOCUS since 1991

Production: WF 7.7.05M, Dev Studio, BID, MRE, WebSphere, DB2 / Test: WF 8.1.05M, App Studio, BI Portal, Report Caster, jQuery, HighCharts, Apache Tomcat, MS SQL Server
C:/ibi/srv76/wfs/bin/HDAYTEST.ERR doesn't work either. Maybe I should mention I'm running this in MRE, not on my local server.

I'll keep playing with it...
L-


7.6.6 on AIX platform
Output: variety (Excel, HTML and PDF)
Did you do what Tom suggested above and make sure you really had data in the hold file?


In Focus since 1993. WebFOCUS 7.7.03 Win 2003
Luminita,

In your fex you should have, for example:
SET HDAY=YR04

You should also have a SET BUSDAY command. for example, because or work week is from Sunday to Thursday, we use:
SET BUSDAY=SMTWT__

The file name MUST be HDAYYR04.err and it must be in the /ibi/srvnn/wfs/bin directory

In the file the dates must have a yyyymmdd format, with one date per line, followed by at least 1 space after which anything else is a comment, e.g. "New Year"


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

Hi,

Hope you guys had a good long weekend!

Yes, I have data in the hold file and it looks something like this:
20080101
20080121
20080218
20080526
20080704
20080901
20081013
20081111
20081127
20081225

I changed the location where the err file is:

APP FI HDAYSWAP DISK /ibi/srv71/wfs/bin/hdayswap.err

I also made sure that I have these 2 lines as suggested:
SET BUSDAYS = _MTWTF_
SET HDAY = swap

Now I get an error when I do the TABLE HOLD:

TABLE FILE SQLHLDY
PRINT HOLIDAY_DATE AS ''
BY HOLIDAY_DATE NOPRINT
ON TABLE HOLD AS HDAYSWAP FORMAT ALPHA
END

0 ERROR AT OR NEAR LINE 23 IN PROCEDURE ADHOCRQ FOCEXEC *
(FOC36219) AN ERROR OCCURED WHEN OPENING FILE: HDAYSWAP

Thank you all for helping me with this. It should be a simple issue, but it's turning into a nightmare...

Luminita


7.6.6 on AIX platform
Output: variety (Excel, HTML and PDF)
This suggests that the file is in the correct directory, but doesn't have the right permissions.


Francis


Give me code, or give me retirement. In FOCUS since 1991

Production: WF 7.7.05M, Dev Studio, BID, MRE, WebSphere, DB2 / Test: WF 8.1.05M, App Studio, BI Portal, Report Caster, jQuery, HighCharts, Apache Tomcat, MS SQL Server
Luminita,

Why are you writing to your holiday file?


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

Hi Daniel,

What I was trying to do is make use of an existent table and create the holiday file using that table. My thought was that since this table will get updated every year with the proper holidays that our company uses, then I don't have to worry about making the changes myself and risk being out of sink. But it might be that my thinking was wrong and that I cannot create a holiday file that way.

Thanks,
Luminita


7.6.6 on AIX platform
Output: variety (Excel, HTML and PDF)
Luminata,

Have you taken a look at the permissions of the file you created? The file probably doesn't have Group Read permission because you created it with your User ID.


Francis


Give me code, or give me retirement. In FOCUS since 1991

Production: WF 7.7.05M, Dev Studio, BID, MRE, WebSphere, DB2 / Test: WF 8.1.05M, App Studio, BI Portal, Report Caster, jQuery, HighCharts, Apache Tomcat, MS SQL Server
Luminita,

This looks like a straight forward task, but you also have to consider that these dates change, Christmas is always Dec 25, but the holiday will be Dec 24 if the 25 is a Saturday and the 26th if it falls on a Sunday, ditto for New Year's and July 4th. Labor day is the first Monday in Sept, Thanksgiving is the 4th Thursday. The Monday holiday bill makes MLK Day and Pres. Day, Memorial Day, etc float. Good luck incorporating all the rules in your program.


Pat
WF 7.6.8, AIX, AS400, NT
AS400 FOCUS, AIX FOCUS,
Oracle, DB2, JDE, Lotus Notes
Hi guys,

Thank you all for helping me, I managed to get this working. My problem was the path to the server, I changed it and then my date calculations worked great.

APP FI HDAYSWAP DISK /servername/ibi/srv71/wfs/bin/hdayswap.err

Pat, as for figuring out all the dates changes, that's why I wanted to use this existent table, to save myself some work :-) That table is maintained and always current, and I can just get the data and use it. Why reinvent the wheel, right?

Thanks again!
Luminita


7.6.6 on AIX platform
Output: variety (Excel, HTML and PDF)
I guess I didn't read you initial post closely enough, I thought you were trying to create a program to update that table.


Pat
WF 7.6.8, AIX, AS400, NT
AS400 FOCUS, AIX FOCUS,
Oracle, DB2, JDE, Lotus Notes