Calculation of DeliveryDate while building Fact
Moderators: chulett, rschirm, roy
-
- Participant
- Posts: 182
- Joined: Thu Jun 16, 2005 2:05 am
Calculation of DeliveryDate while building Fact
Hi All,
I have a business scenario or transformation logic to populate DeliveyDate/ReqiuredDate for an order item.
If a Delivery date is null for the order item, i have to calculate the below logic in DS.
Delivery date is NULL, Get the Creation Date and add +2 days with them to make it as delivery date.
Then you should look on holiday table and it should not falls on weekend as well. If it falls add +1 day and so on until you can find the next business day.
We have built a Holiday Table ffor different resions(US,UK,ANZ,Asia).
Right now, i dont have anything/scenario on how to achieve the same. Kindly through me some lights on the same if you have any.
Thanks.
I have a business scenario or transformation logic to populate DeliveyDate/ReqiuredDate for an order item.
If a Delivery date is null for the order item, i have to calculate the below logic in DS.
Delivery date is NULL, Get the Creation Date and add +2 days with them to make it as delivery date.
Then you should look on holiday table and it should not falls on weekend as well. If it falls add +1 day and so on until you can find the next business day.
We have built a Holiday Table ffor different resions(US,UK,ANZ,Asia).
Right now, i dont have anything/scenario on how to achieve the same. Kindly through me some lights on the same if you have any.
Thanks.
-
- Participant
- Posts: 182
- Joined: Thu Jun 16, 2005 2:05 am
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact:
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact:
-
- Participant
- Posts: 182
- Joined: Thu Jun 16, 2005 2:05 am
Hi Ray,
Yes..Some kind of..We have built a Holiday table for all the regions(US,UK,APAC,ANZ)...which contains Holiday Dates includes national holiday and non-business days(Sat & Sun).
If my Deliverydate is null i have to create the DeliveryDate based on Invoice creationDate + 2 Days which should not fall on the above holiday list.
Eg: If my Invoice creation date is 14-SEP-2018(Friday) the DeliveryDate should be Monday instead of Sunday(can't add +2 days in this case).
Let know your thoughts on the same.
Thanks,
S.R
Yes..Some kind of..We have built a Holiday table for all the regions(US,UK,APAC,ANZ)...which contains Holiday Dates includes national holiday and non-business days(Sat & Sun).
If my Deliverydate is null i have to create the DeliveryDate based on Invoice creationDate + 2 Days which should not fall on the above holiday list.
Eg: If my Invoice creation date is 14-SEP-2018(Friday) the DeliveryDate should be Monday instead of Sunday(can't add +2 days in this case).
Let know your thoughts on the same.
Thanks,
S.R
-
- Participant
- Posts: 182
- Joined: Thu Jun 16, 2005 2:05 am
Hi All,
Please let me know on how to acheive he below logic...
I will make as CreateDate +2 for initial order and CreateDate +3 and so on until if it doesn't falls on my Holiday table....
If i won't find that it in my Holiday table i would like to take that date as my Delivery date...as i have CreateDate+2, CreateDate+3,CreateDate+4...If it doesn't falls on any date i would like to pickup the First come first serve basis...Pls me know your thoughts on the same...
Thanks & Regards,
S.R
Please let me know on how to acheive he below logic...
I will make as CreateDate +2 for initial order and CreateDate +3 and so on until if it doesn't falls on my Holiday table....
If i won't find that it in my Holiday table i would like to take that date as my Delivery date...as i have CreateDate+2, CreateDate+3,CreateDate+4...If it doesn't falls on any date i would like to pickup the First come first serve basis...Pls me know your thoughts on the same...
Thanks & Regards,
S.R
As Ray noted, a lookup (sparse or otherwise) against your Calendar table - for all possible dates forward. Meaning the most weekend/holiday dates you could have in a row, what is that four? Then check them in order and take the first one that satisfies your rules, including your rule for what happens if none of them do.
At least that's the first thing that popped into my head reading this again.
At least that's the first thing that popped into my head reading this again.
-craig
"You can never have too many knives" -- Logan Nine Fingers
"You can never have too many knives" -- Logan Nine Fingers
-
- Participant
- Posts: 182
- Joined: Thu Jun 16, 2005 2:05 am
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact:
Another possibility would be to perform a range lookup. Your calendar table, shorn of non-business days, would be the reference data set, and the range would be greater than current date and less than or equal to high date (generated in the Transformer stage upstream of the Lookup stage). You could keep the country code in the lookup (as an "equals" lookup) if that's useful.
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.