Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
mickartik
Contributor III
Contributor III

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.

1 Solution

Accepted Solutions
sunny_talwar

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

View solution in original post

4 Replies
sunny_talwar

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

sunny_talwar

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;

mickartik
Contributor III
Contributor III
Author

Thank you very much Sunny. This really helped.

sunny_talwar

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