topic matching the value in the interval in QlikView

matching the value in the interval

mickartik — Thu, 14 Jan 2016 05:20:47 GMT

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.

Re: matching the value in the interval

sunny_talwar — Thu, 14 Jan 2016 17:49:57 GMT

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;

Re: matching the value in the interval

sunny_talwar — Thu, 14 Jan 2016 17:53:12 GMT

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;

Re: matching the value in the interval

mickartik — Mon, 18 Jan 2016 08:50:51 GMT

Thank you very much Sunny. This really helped.

Re: matching the value in the interval

sunny_talwar — Mon, 18 Jan 2016 10:08:45 GMT

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