Date check for business validation
Moderators: chulett, rschirm, roy
-
- Participant
- Posts: 221
- Joined: Fri Feb 17, 2006 3:38 am
- Location: India
- Contact:
Date check for business validation
Hello, i am migrating data from SUN ERP to SAP for Purchase Orders. The condition is delivery date should be, Current system date + 1 month. But the problem is after adding 1 month, the resulting date should not have a day falling on Saturday or Sunday.
Is there any function or routine for this
Is there any function or routine for this
Thanks & Regards
Parag Saundattikar
Certified for Infosphere DataStage v8.0
Parag Saundattikar
Certified for Infosphere DataStage v8.0
There are many ways of doing this and the optimal one depends upon the type and format of your source and where you are doing this comparison.
How do you want to add a month? Add 30 days or take the current date and change the month part. If it does fall on a weekend, do you want to take the Friday before or the Monday after or fail?
How do you want to add a month? Add 30 days or take the current date and change the month part. If it does fall on a weekend, do you want to take the Friday before or the Monday after or fail?
<a href=http://www.worldcommunitygrid.org/team/ ... TZ9H4CGVP1 target="WCGWin">
</a>
</a>
-
- Participant
- Posts: 221
- Joined: Fri Feb 17, 2006 3:38 am
- Location: India
- Contact:
Date Validation
Thanks ArndW,ArndW wrote:There are many ways of doing this and the optimal one depends upon the type and format of your source and where you are doing this comparison.
How do you want to add a month? Add 30 days or take the current date and change the month part. If it does fall on a weekend, do you want to take the Friday before or the Monday after or fail?
Actually i am adding 1 month to the current system date as DATE.TAG(Date()+30). Now i want to check that if the resulting date falls on saturday or sunday then i should take monday.
Can you please help
Thanks & Regards
Parag Saundattikar
Certified for Infosphere DataStage v8.0
Parag Saundattikar
Certified for Infosphere DataStage v8.0
You would use the OCONV function and the 'DW' conversion to get the DOW with 1 being Monday.
Code: Select all
IF OCONV(DATE()+30,'DW') >5 THEN "Weekend" ELSE "Weekday"
<a href=http://www.worldcommunitygrid.org/team/ ... TZ9H4CGVP1 target="WCGWin">
</a>
</a>
-
- Participant
- Posts: 221
- Joined: Fri Feb 17, 2006 3:38 am
- Location: India
- Contact:
Date Check
Hey thanx Arndw,ArndW wrote:You would use the OCONV function and the 'DW' conversion to get the DOW with 1 being Monday.
Code: Select all
IF OCONV(DATE()+30,'DW') >5 THEN "Weekend" ELSE "Weekday"
Just one more trouble i wanted to give you.
1st is what value should be for "Weekend" and "Weekday" that u mentioned in then and else clause respectively
and 2nd is, Can you also tell me the scenario for taking friday
Thanks & Regards
Parag Saundattikar
Certified for Infosphere DataStage v8.0
Parag Saundattikar
Certified for Infosphere DataStage v8.0
Parag,
were you looking for advice on how to do this or were you looking for a solution?
You now have the information you need - you can get the numeric day of week with the OCONV(juliandate,'DW', so if you want to handle Friday in addition to Sat and Sun you would change your condition to read ">4" instead of ">5" so days 5,6 and 7 go into one branch of the IF while other days go to the other.
So now you have "Weekend" and "Weekday"; the "Weekday" portion is easy since the date returned is valid, you would want to use your DATE.TAG conversion as you've stated. The "Weekend" branch needs to modify the date - but you haven't said what your rule is supposed to be.
were you looking for advice on how to do this or were you looking for a solution?
You now have the information you need - you can get the numeric day of week with the OCONV(juliandate,'DW', so if you want to handle Friday in addition to Sat and Sun you would change your condition to read ">4" instead of ">5" so days 5,6 and 7 go into one branch of the IF while other days go to the other.
So now you have "Weekend" and "Weekday"; the "Weekday" portion is easy since the date returned is valid, you would want to use your DATE.TAG conversion as you've stated. The "Weekend" branch needs to modify the date - but you haven't said what your rule is supposed to be.
<a href=http://www.worldcommunitygrid.org/team/ ... TZ9H4CGVP1 target="WCGWin">
</a>
</a>
Hi parag.s.27,
welcome aboard.One more solution
You can find the day of Delivery_Date by using Oconv(Delivery_Date , "DWA")
Declare Stage variables
Delivery Date ---S1
Delivery Date+1 ---S2
Delivery Date+2 ---S3
In your column Derivation,
If Oconv(Delivery_Date , "DWA") ="Saturday"
then Oconv(S3, "DWA") elseif
Oconv(Delivery_Date , "DWA") = "Sunday"
then Oconv(S2, "DWA") else
Oconv(Delivery_Date , "DWA")
regards
kcs
welcome aboard.One more solution
You can find the day of Delivery_Date by using Oconv(Delivery_Date , "DWA")
Declare Stage variables
Delivery Date ---S1
Delivery Date+1 ---S2
Delivery Date+2 ---S3
In your column Derivation,
If Oconv(Delivery_Date , "DWA") ="Saturday"
then Oconv(S3, "DWA") elseif
Oconv(Delivery_Date , "DWA") = "Sunday"
then Oconv(S2, "DWA") else
Oconv(Delivery_Date , "DWA")
regards
kcs
kcs,
I like your suggestion, but if you stick with DW conversion and an integer you can make your logic simpler, this assumes you will make your order go to the Friday before a weekend, see:
I like your suggestion, but if you stick with DW conversion and an integer you can make your logic simpler, this assumes you will make your order go to the Friday before a weekend, see:
Code: Select all
StageVar "DW" = OCONV(Date()+30,'DW')
StageVar "DWCorrection" = IF DW >5 THEN DW-5 ELSE 0
StageVar "NextMonthValidDay" = DATE.TAG(date()+30-DWCorrection))
<a href=http://www.worldcommunitygrid.org/team/ ... TZ9H4CGVP1 target="WCGWin">
</a>
</a>
Wolfgang - we shouldn't be so Euro-centric and should add in all those South American, Middle-eastern as well as Asian holidays as well
<a href=http://www.worldcommunitygrid.org/team/ ... TZ9H4CGVP1 target="WCGWin">
</a>
</a>
-
- Participant
- Posts: 221
- Joined: Fri Feb 17, 2006 3:38 am
- Location: India
- Contact:
Date Check
Hi All,ArndW wrote:kcs,
I like your suggestion, but if you stick with DW conversion and an integer you can make your logic simpler, this assumes you will make your order go to the Friday before a weekend, see:
Code: Select all
StageVar "DW" = OCONV(Date()+30,'DW') StageVar "DWCorrection" = IF DW >5 THEN DW-5 ELSE 0 StageVar "NextMonthValidDay" = DATE.TAG(date()+30-DWCorrection))
Thanks for suggestions. i tried method of 'DW', but dont knw why data stage is still returning the same date i.e System Date + 30 days without any modifications. don't know why but validations are not working, even if the OCONV(Date()+30,'DW') returns proper value of day of week
Any way thanks for suggestions
Thanks & Regards
Parag Saundattikar
Certified for Infosphere DataStage v8.0
Parag Saundattikar
Certified for Infosphere DataStage v8.0
-
- Participant
- Posts: 221
- Joined: Fri Feb 17, 2006 3:38 am
- Location: India
- Contact:
Re: Date Check
[quote="parag.s.27"][quote="ArndW"]kcs,
Hey Thanks to all of you. It finally worked. I just made a Routine for it.
Thanks
Hey Thanks to all of you. It finally worked. I just made a Routine for it.
Thanks
Thanks & Regards
Parag Saundattikar
Certified for Infosphere DataStage v8.0
Parag Saundattikar
Certified for Infosphere DataStage v8.0
Re: Date Check
Your calculation's will not be right if an Holiday falls on a date during the week. if it falls on the week day that you get at the end of your calcuation then you will be having a wrong date.parag.s.27 wrote:Hi All,ArndW wrote:kcs,
I like your suggestion, but if you stick with DW conversion and an integer you can make your logic simpler, this assumes you will make your order go to the Friday before a weekend, see:
Code: Select all
StageVar "DW" = OCONV(Date()+30,'DW') StageVar "DWCorrection" = IF DW >5 THEN DW-5 ELSE 0 StageVar "NextMonthValidDay" = DATE.TAG(date()+30-DWCorrection))
Thanks for suggestions. i tried method of 'DW', but dont knw why data stage is still returning the same date i.e System Date + 30 days without any modifications. don't know why but validations are not working, even if the OCONV(Date()+30,'DW') returns proper value of day of week
Any way thanks for suggestions
Any way if your date tag does not work try the basic Oconv.It worked for me.
You can try Oconv((@DATE+30-DWCorrection),"D-YMD")
my two cents
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact:
Holidays - of necessity - must be table (or hashed file) driven. Some holidays change year to year, such as Ramadan or Easter.
Left(Oconv(TheDate, "DWB"),1) = "S" is a convenient test for a weekend day in English.
Left(Oconv(TheDate, "DWB"),1) = "S" is a convenient test for a weekend day in English.
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.
-
- Participant
- Posts: 221
- Joined: Fri Feb 17, 2006 3:38 am
- Location: India
- Contact:
Date check
Hi,ray.wurlod wrote:Holidays - of necessity - must be table (or hashed file) driven. Some holidays change year to year, such as Ramadan or Easter.
Left(Oconv(TheDate, "DWB"),1) = "S" is a convenient test for a weekend day in English.
Actually i have to take care of weekends only. For rest all holidays, our SAP team has some validation checks while loading data into it. I am generating target files as per SAP input format.
Thanks & Regards
Parag Saundattikar
Certified for Infosphere DataStage v8.0
Parag Saundattikar
Certified for Infosphere DataStage v8.0