topic Re: Create continous price history per item, last known value in App Development

Create continous price history per item, last known value

Anonymous — Fri, 11 Aug 2017 11:01:07 GMT

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

Re: Create continous price history per item, last known value

sunny_talwar — Fri, 11 Aug 2017 11:35:26 GMT

May be check this out

Generating Missing Data In QlikView

Re: Create continous price history per item, last known value

Anonymous — Tue, 15 Aug 2017 09:30:57 GMT

Hi Sunny,

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

Regards

Birkir

Re: Create continous price history per item, last known value

Anonymous — Tue, 15 Aug 2017 10:08:39 GMT

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

Re: Create continous price history per item, last known value

sunny_talwar — Tue, 15 Aug 2017 11:06:49 GMT

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));