Create continous price history per item, last known value

[Anonymous]

Hi,

I have a price history table with in our ERP system and I need to create an application which can give us the correct price and any given date from a master calendar.  So if a value is missing for a date then fetch the last known value.  This might be done in set expressions or Load. Maybe previous() could work?

The table looks something like this

ItemNo	PriceDate	CostPrice	SalesPrice
1	2003-02-10 00:00:00.000	100	110
2	2003-02-10 00:00:00.000	90	120
1	2004-02-10 00:00:00.000	90	105
2	2005-02-10 00:00:00.000	80	100
1	2005-02-10 00:00:00.000	100	105
1	2005-02-15 00:00:00.000	100	120

And the result should be something like this. The .(dots) represent data in between.

ItemNo	PriceDate	CostPrice	SalesPrice
1	2003-02-10 00:00:00.000	100	110
1	2003-02-11 00:00:00.000	100	110
1	2003-02-12 00:00:00.000	100	110
1	2003-02-13 00:00:00.000	100	110
1	2003-02-14 00:00:00.000	100	110
1	2003-02-15 00:00:00.000	100	110
1	2003-02-16 00:00:00.000	100	110
1	2003-02-17 00:00:00.000	100	110
1	.	.	.
1	.	.	.
1	2004-02-10 00:00:00.000	90	105
1	2004-02-11 00:00:00.000	90	105
1	2004-02-12 00:00:00.000	90	105

Any hints are welcome

Kind Regards

Birkir    

[sunny_talwar · Accepted solution]

May be check this out

Generating Missing Data In QlikView

[Anonymous]

Hi Sunny,

Thanks for pointing this out. This Paper had the answer and this is working. This paper is a must read.

Regards

Birkir    

[Anonymous]

Hi again,

I have one question to add. The data involved will be far to large is seems so I was thinking off only generating enries for the 1st day of each month. Does anyone have an idea on how to change this to only add the 1st date of everymonth?

// ---- B: Create all combinations of product and date

TempProduct_x_Dates:

Load distinct ProductID Resident TempProductBalances;

Join (TempProduct_x_Dates)

Load Date(recno()+$(vMinDate)) as DateKey Autogenerate vMaxDate - vMinDate;

Regards

Birkir    

[sunny_talwar]

May be this

TempProduct_x_Dates:

LOAD DISTINCT ProductID

Resident TempProductBalances;

Join (TempProduct_x_Dates)

LOAD Date(MonthStart($(vMinDate), IterNo()-1)) as DateKey

Autogenerate 1

While MonthStart($(vMinDate), RecNo()-1) <= MonthStart($(vMaxDate));