4 Replies Latest reply: Jan 18, 2016 5:08 AM by Sunny Talwar

# matching the value in the interval

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??

• ###### Re: matching the value in the interval

Check this out:

Table:

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:

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:

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);

IntervalMatch(Date)

[Effective To]

Resident FinalTable;

• ###### Re: matching the value in the interval

Slight improvement to the code:

Table:

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:

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:

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);

IntervalMatch(Date)

[Effective To]

Resident FinalTable;

• ###### Re: matching the value in the interval

Thank you very much Sunny. This really helped.

• ###### Re: matching the value in the interval

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