Hi All,
I need to implement a logic where in i feel i would have to do a lookup in a loop. The requirement is something like this:
I have an input date and i check if the data is a weekend or a country holiday. I am checking the weekend by using WeekdayFromDate function and for checking country holiday i am doing a lookup on the holiday calendar table. If the date is a weekend or a holiday i offset it accordingly to get a normal business day. However once i am done with the offset i have to check again the weekend and holiday logic and for checking the holiday i need to do the lookup again. This process i need to repeat till it is a working day.
For example, let us assume the input date is 5th April 2013 and it is a country holiday so i offset it by 1 and end up with a weekend. So, i check it again for weekend and offset it by 2 to get to Monday 8th April 2013. Incidentally Monday is again a holiday and i need to offset the date again by 1 day and get the business day. Once offset done then check again for weekend and holiday. So basically i need to increment the date till i get a normal business day.
I hope the above example is helpful.
Can i request for some help in this regard. Like how do i lookup the holiday table in a loop and increment the date till i get a valid business day. Any help is highly appreciated.
Thank you,
Punar
Lookup in a loop
Moderators: chulett, rschirm, roy
You can't loop data back around within a single job...DataStage will complain that you have a data cycle detected and will abort the job.
If you are using IS 8.5 or above, you may be able to use transformer looping to accomplish this by placing all of the holidays into a single delimited string (this could be passed in as a job parameter), then using the Index() command to search that string for your date. Just repeat the loop until you no longer hit a weekend or holiday.
Example holiday dates string: |2013-01-01|2013-03-20|2013-12-25|
Comparison logic: If Index(svHolidays,svWeekendCheckDate,1) > 0 then x else y
You will have to utilize stage and loop variables to get the logic to work, and there will be conversion back and forth between Date and String data types, but I believe it's doable.
If you're using an earlier release of IS, you can accomplish the same using either a BuildOp, Custom Operator or an external filter to call an outside script or program, and the logic in those could actually be easier than in a Transformer.
Regards,
If you are using IS 8.5 or above, you may be able to use transformer looping to accomplish this by placing all of the holidays into a single delimited string (this could be passed in as a job parameter), then using the Index() command to search that string for your date. Just repeat the loop until you no longer hit a weekend or holiday.
Example holiday dates string: |2013-01-01|2013-03-20|2013-12-25|
Comparison logic: If Index(svHolidays,svWeekendCheckDate,1) > 0 then x else y
You will have to utilize stage and loop variables to get the logic to work, and there will be conversion back and forth between Date and String data types, but I believe it's doable.
If you're using an earlier release of IS, you can accomplish the same using either a BuildOp, Custom Operator or an external filter to call an outside script or program, and the logic in those could actually be easier than in a Transformer.
Regards,
- james wiles
All generalizations are false, including this one - Mark Twain.
All generalizations are false, including this one - Mark Twain.