Qlik Community

Qlik Sense App Development

Discussion board where members can learn more about Qlik Sense App Development and Usage.

Announcements
Qlik® Product Spotlight: Discover what’s possible. Get more from our products.
See for yourself. Register today.
birkirbjorns
New Contributor III

Create continous price history per item, last known value

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

   

ItemNoPriceDateCostPriceSalesPrice
12003-02-10 00:00:00.000100110
22003-02-10 00:00:00.00090120
12004-02-10 00:00:00.00090105
22005-02-10 00:00:00.00080100
12005-02-10 00:00:00.000100105
12005-02-15 00:00:00.000100120

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

   

ItemNoPriceDateCostPriceSalesPrice
12003-02-10 00:00:00.000100110
12003-02-11 00:00:00.000100110
12003-02-12 00:00:00.000100110
12003-02-13 00:00:00.000100110
12003-02-14 00:00:00.000100110
12003-02-15 00:00:00.000100110
12003-02-16 00:00:00.000100110
12003-02-17 00:00:00.000100110
1...
1...
12004-02-10 00:00:00.00090105
12004-02-11 00:00:00.00090105
12004-02-12 00:00:00.00090105

Any hints are welcome

Kind Regards

Birkir    

1 Solution

Accepted Solutions

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

4 Replies

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

birkirbjorns
New Contributor III

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

Hi Sunny,

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

Regards

Birkir    

birkirbjorns
New Contributor III

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

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

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