Stage Variables

[poorna_76]

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

[kcbland]

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.

[dsxdev]

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

Code: Select all

Mod(DaysSinceFromDate(InputDate, "1900-01-01"),7)

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.

[ray.wurlod]

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.