Skip to main content

App Development

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

Announcements
QlikWorld 2023, a live, in-person thrill ride. Save $300 before February 6: REGISTER NOW!
cancel
Showing results for 
Search instead for 
Did you mean: 
birkirbjorns
Contributor III
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
4 Replies
sunny_talwar

birkirbjorns
Contributor III
Contributor III
Author

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
Contributor III
Contributor III
Author

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