Lookup value on load

[#1]

I have a custom billing calendar that has the following fields: BillingYear, BeginDate, EndDate, and Period and I'm trying to do a lookup into the Billing Calendar when loading my fact data to grab the Billing Year and Period fields based on TransDate. There are no associated fields between Billing Calendar and fact data. So when I load a record, I want to evaluate the TransDate against the BeginDate and EndDate in the Billing Calendar table and get the related Billing Year and Period. Thoughts?

Thanks - Kevin

[#2 · Accepted solution]

Take a look at attachment. There are two approach. Comment/Uncomment one approach to see another one.

[#3]

Perfect Nick. thanks for the input.

Kevin