Calculation of DeliveryDate while building Fact

Post questions here relative to DataStage Enterprise/PX Edition for such areas as Parallel job design, Parallel datasets, BuildOps, Wrappers, etc.

Moderators: chulett, rschirm, roy

Post Reply
satheesh_color
Participant
Posts: 182
Joined: Thu Jun 16, 2005 2:05 am

Calculation of DeliveryDate while building Fact

Post by satheesh_color »

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.
chulett
Charter Member
Charter Member
Posts: 43085
Joined: Tue Nov 12, 2002 4:34 pm
Location: Denver, CO

Post by chulett »

Any specifics on which aspect of this you need help with? The "add 2 days" part shouldn't be a problem so I assume it's all about the Holiday / Weekend calendar check, yes?
-craig

"You can never have too many knives" -- Logan Nine Fingers
satheesh_color
Participant
Posts: 182
Joined: Thu Jun 16, 2005 2:05 am

Post by satheesh_color »

Hi Craig,

You are absolutely with me.....Yes.






Thanks.
S.R
ray.wurlod
Participant
Posts: 54607
Joined: Wed Oct 23, 2002 10:52 pm
Location: Sydney, Australia
Contact:

Post by ray.wurlod »

Sounds like you need a sparse lookup against your calendar table.
IBM Software Services Group
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.
ray.wurlod
Participant
Posts: 54607
Joined: Wed Oct 23, 2002 10:52 pm
Location: Sydney, Australia
Contact:

Post by ray.wurlod »

Sounds like you need a sparse lookup against your calendar table.
IBM Software Services Group
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.
satheesh_color
Participant
Posts: 182
Joined: Thu Jun 16, 2005 2:05 am

Post by satheesh_color »

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
satheesh_color
Participant
Posts: 182
Joined: Thu Jun 16, 2005 2:05 am

Post by satheesh_color »

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
chulett
Charter Member
Charter Member
Posts: 43085
Joined: Tue Nov 12, 2002 4:34 pm
Location: Denver, CO

Post by chulett »

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.
-craig

"You can never have too many knives" -- Logan Nine Fingers
satheesh_color
Participant
Posts: 182
Joined: Thu Jun 16, 2005 2:05 am

Post by satheesh_color »

Great Thanks Craig & Ray !!!


I have done / achieved the result by taking CreateDate + 2, CreateDate+3,....until CreateDate+7(Max 7 Days from the order CreatioDate as per the Business) and lookedup against Holiday Table and fetch the DeliveryDate


I looks good as of now !!!




Regards,
S.R
ray.wurlod
Participant
Posts: 54607
Joined: Wed Oct 23, 2002 10:52 pm
Location: Sydney, Australia
Contact:

Post by ray.wurlod »

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.
Post Reply