Determine price at date without a datapoint

[#1]

I have a list of articles with price change on several dates. No I would like to know what the price was on 2 dates that are not in that list.

Article A	28-04-2008	1.21
Article A	19-03-2011	1.31
Article A	20-12-2012	1.33
Article A	2-1-2013	1.40
Article B	1-1-2007	2.40
Article B	31-5-2009	2.35
Article B	28-04-2011	2.89
Article B	01-02-2013	2.77

I would like to know the valid price at 31-12-2011 and the valid price at 31-12-2012 for each article.

My set of articles is about 60K unique article codes.

Anyone have a good Idea how I could tackle this?

Thank you very much for your help

[#2]

Maybe you can get the latest price prior to the date you want or the next price.

[Gysbert_Wassenaar · Accepted solution]

See this blog post for a solution

[rwunderlich]

Also see the QV Cookbook example "Fill values in a data range using previous values.". That example uses a pricing example just like your question. You can download the QV Cookbook from

http://robwunderlich.com/downloads/

-Rob

http;//robwunderlich

[#5]

Rob,

Thanks very much for the heads up on your cookbook it really helps...

I have tried to apply your script and it seems to work. However when I look at the table viewer it tells me that this generates over 24K rows while it should create much less. being 2 articles (products) times 2100 dates is 4200 rows... for some reasons it says it generates 24K... I don't understand.

The reason this is a problem is because I 'm using this on 4300 distinct products with the 2100 dates.. this should result in about 8000K records or so which is fine. however at them moment it creates about 300.000K records... and my CPU is not appreciating that..

could you perhaps help me get one step further

Thanks..

Reason I'm using this is because I want to calculate the margin difference between the beginning and the end of a period uding the purchasing price. This in adition to this thread for you http://community.qlik.com/message/313808#313808

Thanks for the reply

[rwunderlich]

You are getting the right number of DISTINCT rows

count(DISTINCT Date & Month & Price_AH & Product) = 4212

But many of the rows are duplicated in the final table. The duplicates are in sets of 2, 16, 32 and 50. I can't quite figure out where they are coming from. There are no duplicates in your input table, right Can you post your input spreadsheet?

-Rob

http://masterssummit.com

[#7]

indeed I'm getting the right results when I count distinct.

here is the input table.

Thanks for helping

[rwunderlich]

The example I pointed you to in the cookbook actually has an error. It should be an Extended IntervalMatch but it didn't matter with the test dataset.

I've updated your attached example to use the Extended sytax and it's generating the right number of rows now.

See attached.

-Rob

Additions:

// Join Products to PriceMaster

JOIN (PriceMaster)

LOAD DISTINCT Product

RESIDENT PriceTransactions

;

// Additon of product to the match

LEFT JOIN (PriceMaster) IntervalMatch(Date, Product)

LOAD StartDate, EndDate, Product

RESIDENT PriceTransactions

;

[#9]

Great,

problem solved and thanks for the advice.... When can I download the new version of the cookbook

regards

Bas