Page 1 of 1

Calculation of DeliveryDate while building Fact

Posted: Fri Sep 14, 2018 8:06 am
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.

Posted: Fri Sep 14, 2018 8:58 am
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?

Posted: Fri Sep 14, 2018 10:39 am
by satheesh_color
Hi Craig,

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






Thanks.
S.R

Posted: Fri Sep 14, 2018 8:15 pm
by ray.wurlod
Sounds like you need a sparse lookup against your calendar table.

Posted: Fri Sep 14, 2018 8:17 pm
by ray.wurlod
Sounds like you need a sparse lookup against your calendar table.

Posted: Sat Sep 15, 2018 3:44 am
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

Posted: Sun Sep 16, 2018 10:22 am
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

Posted: Sun Sep 16, 2018 1:00 pm
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.

Posted: Sun Sep 16, 2018 6:34 pm
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

Posted: Sun Sep 16, 2018 9:12 pm
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.