Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi,
Can someone help me out on this?
I've table with Product, Price & effective date. I've to find out the price on a particular date.
Sample data:
Product | Price | Effective From |
P1 | 50 | 7-Jan-10 |
P1 | 70 | 13-Jan-10 |
P1 | 100 | 25-Jan-10 |
P2 | 40 | 6-Jan-10 |
P2 | 80 | 14-Jan-10 |
P2 | 110 | 24-Jan-10 |
So if I select 8-Jan-10 then I should get
Product | Price |
P1 | 50 |
P2 | 40 |
Can it be done in using intervalmatch, if yes can you please give me an example??
Thanks in advance.
No problem. I am glad I was able to help.
If you got what you were looking for, would you be able to close this thread down by selecting the correct answer.
Best,
Sunny
Check this out:
Table:
LOAD * Inline [
Product, Price, Effective From
P1, 50, 7-Jan-10
P1, 70, 13-Jan-10
P1, 100, 25-Jan-10
P2, 40, 6-Jan-10
P2, 80, 14-Jan-10
P2, 110, 24-Jan-10
];
FinalTable:
LOAD *,
Date(If(Previous(Product) = Product, Previous([Effective From]), [Effective From])) as [Effective To]
Resident Table
Order By Product, [Effective From] desc;
DROP Table Table;
MinMax:
LOAD Min([Effective From]) as Min,
Max([Effective From]) as Max
Resident FinalTable;
LET vMin = Peek('Min');
LET vMax = Peek('Max');
DROP Table MinMax;
Calendar:
LOAD Date($(vMin) + IterNo() - 1) as Date
AutoGenerate 1
While $(vMin) + IterNo() -1 <= $(vMax);
Link:
IntervalMatch(Date)
LOAD [Effective From],
[Effective To]
Resident FinalTable;
Slight improvement to the code:
Table:
LOAD * Inline [
Product, Price, Effective From
P1, 50, 7-Jan-10
P1, 70, 13-Jan-10
P1, 100, 25-Jan-10
P2, 40, 6-Jan-10
P2, 80, 14-Jan-10
P2, 110, 24-Jan-10
];
FinalTable:
LOAD *,
Date(If(Previous(Product) = Product, Previous([Effective From]) - 1, [Effective From])) as [Effective To]
Resident Table
Order By Product, [Effective From] desc;
DROP Table Table;
MinMax:
LOAD Min([Effective From]) as Min,
Max([Effective From]) as Max
Resident FinalTable;
LET vMin = Peek('Min');
LET vMax = Peek('Max');
DROP Table MinMax;
Calendar:
LOAD Date($(vMin) + IterNo() - 1) as Date
AutoGenerate 1
While $(vMin) + IterNo() -1 <= $(vMax);
Link:
IntervalMatch(Date)
LOAD [Effective From],
[Effective To]
Resident FinalTable;
Thank you very much Sunny. This really helped.
No problem. I am glad I was able to help.
If you got what you were looking for, would you be able to close this thread down by selecting the correct answer.
Best,
Sunny