Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
May be check this out
Hi Sunny,
Thanks for pointing this out. This Paper had the answer and this is working. This paper is a must read.
Regards
Birkir
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
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));