Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

kparidamt
New Contributor

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: 

ProductPriceEffective From
P1507-Jan-10
P17013-Jan-10
P110025-Jan-10
P2406-Jan-10
P28014-Jan-10
P211024-Jan-10

So if I select 8-Jan-10 then I should get

 

ProductPrice
P150
P240

Can it be done in using intervalmatch, if yes can you please give me an example??

Thanks in advance.

Tags (1)
1 Solution

Accepted Solutions

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

4 Replies

Re: matching the value in the interval

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;


Capture.PNG

Re: matching the value in the interval

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;

kparidamt
New Contributor

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

Community Browser