can this be done?

[#1]

Hi,

I have been struggling with this issue for past couple of days.

here is the sample data in my fact table (joined using time data and po data)

Time entry Date time entered PO Number Rate

01/01/2010 8 A001 75

01/07/2010 8 A001 75

01/15/2010 8 A001 75

01/01/2010 8 A002 80

I have another table (PO rate change) that maintains the rate change data

so for instance

Change effective date PO Number New Rate Old Rate

01/05/2010 A001 65 60

01/10/2010 A001 75 65

so basically the fact table always shows the current rate. now to calculate billed amount, i need to take each record from the fact table, compare the 'time entry date' with the 'change effective date' from 'po rate change table' to get the correct rate at that time. for example, for the first entry (A001 - 01/01/2010) in the fact table I will have to use rate of 60 as it was only changed to 65 on 01/05. for the second entry (01/07/2010-A001) i will have to use the rate 65. and for the next entry (01/15/2010-A001) i will have to use 75. for the 4th entry (A002-01/01/2010) I will have to use the rate 80 from the fact table as it has not changed and hence is not maintained in the rate change table.

Can this be achieved in the Load script or the chart expressions?

thanks,

manoj agrawal

[#2 · Accepted solution]

It can using Intervalmatch. You will need two date fields for your comparsion range in the rate reference table so you may need to manipulate the load script for your price reference table to include not only the date the price changed but also the first date the price was effective. From there, the intervalmatch will compare your transaction date to the table of dates in your reference table as well as some keys if you have multiple date ranges for different products, etc and join the tables using fields you specify.

Detailed documentation for it is on page 479 of Book 1 in the reference manual.

[#3]

Hi Aaron,

Thanks for the reply and sorry for the delay in verification. Yes, i got it working using interval match but it took me a while to figure it out.

thanks again...