Discussion Board for collaboration on QlikView Scripting.
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.
So if I select 8-Jan-10 then I should get
Can it be done in using intervalmatch, if yes can you please give me an example??
Thanks in advance.
Go to Solution.
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.
Check this out:
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
Date(If(Previous(Product) = Product, Previous([Effective From]), [Effective From])) as [Effective To]
Order By Product, [Effective From] desc;
DROP Table Table;
LOAD Min([Effective From]) as Min,
Max([Effective From]) as Max
LET vMin = Peek('Min');
LET vMax = Peek('Max');
DROP Table MinMax;
LOAD Date($(vMin) + IterNo() - 1) as Date
While $(vMin) + IterNo() -1 <= $(vMax);
LOAD [Effective From],
Slight improvement to the code:
Date(If(Previous(Product) = Product, Previous([Effective From]) - 1, [Effective From])) as [Effective To]
Thank you very much Sunny. This really helped.