Hii All,
I have requirement like this:
Date,BusinessDayInd,WeekDayInd,NextBusinessDay
10/28/2004,Y,Y,---------
10/29/2004,Y,Y,---------
10/30/2004,N,N,----------
10/31/2004,N,N,---------
11/01/2004,Y,Y,----------
Here i have to set the "NextBusinessDay" based on the "BusinessDayInd".
Here if we take 10/29/2004,
the next BusinessDay for that is 11/01/2004.
I have to set 11/01/2004 for 10/29/2004
10/30/2004
10/31/2004
(since for all of these next Business day is 11/01/2004)
I tried writting Stage Variables, but could not get the correct Results.
Can any one help me.
Thanks in Advance.
Poorna
Stage Variables
Moderators: chulett, rschirm, roy
Put all of your source data into a hash file and use the date as the key. Then, read the source again and reference the hash file four times. In each reference key expression, use your date value +1, +2, +3, and +4. Since most next business days will be the next day, on 5/7 attempts the +1 row will have the business day indicator set. On 3 day weekends, it will be the +2 row, on 4 day weekends, it will be the +3 day, and rarely will it be the +4 day.
So, you give preference to the +1 row, if its business day indicator is set use that row, else check the +2 row, etc.
So, you give preference to the +1 row, if its business day indicator is set use that row, else check the +2 row, etc.
Kenneth Bland
Rank: Sempai
Belt: First degree black
Fight name: Captain Hook
Signature knockout: right upper cut followed by left hook
Signature submission: Crucifix combined with leg triangle
Rank: Sempai
Belt: First degree black
Fight name: Captain Hook
Signature knockout: right upper cut followed by left hook
Signature submission: Crucifix combined with leg triangle
What is NextBuisnessDay?
Do you mean that if the date is any weak day other than Saturday and Sunday then it is a BuisnessDay
In that case you can do on thing 1900-01-01 is monday.
So
you can use the code
Now based on the Mod value you can decide whether the next date is Buisness day or not then just set the value for example
if you get mod value as 0 then it is monday so set next date as NextBuisbnessDay,
If the return value is 5 or 6 then add 2 days to the input date and you'll get next Buisness day.
Do you mean that if the date is any weak day other than Saturday and Sunday then it is a BuisnessDay
In that case you can do on thing 1900-01-01 is monday.
So
you can use the code
Code: Select all
Mod(DaysSinceFromDate(InputDate, "1900-01-01"),7)
if you get mod value as 0 then it is monday so set next date as NextBuisbnessDay,
If the return value is 5 or 6 then add 2 days to the input date and you'll get next Buisness day.
Happy DataStaging
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact:
Lookahead in a delimited text file is possible (using SQL via ODBC driver for text files) but horrendously slow.
Ken's suggestion (using a hashed file) is the best solution. The nice thing about this approach is that DataStage internal date format is an integer, which makes it so easy to get day+1, day+2, and so on.
Create as many lookups as the longest possible range of non-business days (this might be as many as five or six), and use the first non-null return value. Enable shared hashed file memory cache so that only one copy of the hashed file is cached.
Ken's suggestion (using a hashed file) is the best solution. The nice thing about this approach is that DataStage internal date format is an integer, which makes it so easy to get day+1, day+2, and so on.
Create as many lookups as the longest possible range of non-business days (this might be as many as five or six), and use the first non-null return value. Enable shared hashed file memory cache so that only one copy of the hashed file is cached.
IBM Software Services Group
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.