Forum Discussion
Transforming Sporadic Forecast Data
I need help transforming some sporadic data I have in a table. I need it in a new table with all the products but with new rows for every date. The latest forecast is used for the new rows. Any help would be appreciated. Thanks.
Example:
| Start | End | ||||||
| Product | forecast | date | Product | forecast | date | ||
| rice | 10 | 1/1/2020 | rice | 10 | 1/1/2020 | ||
| milk | 5 | 1/3/2020 | rice | 10 | 1/2/2020 | ||
| eggs | 2 | 1/5/2020 | rice | 10 | 1/3/2020 | ||
| rice | 11 | 1/7/2020 | rice | 10 | 1/4/2020 | ||
| eggs | 5 | 1/7/2020 | rice | 10 | 1/5/2020 | ||
| rice | 9 | 1/9/2020 | rice | 10 | 1/6/2020 | ||
| milk | 3 | 1/9/2020 | rice | 11 | 1/7/2020 | ||
| rice | 11 | 1/8/2020 | |||||
| rice | 9 | 1/9/2020 | |||||
| rice | 9 | 1/10/2020 | |||||
| milk | 5 | 1/3/2020 | |||||
| milk | 5 | 1/4/2020 | |||||
| milk | 5 | 1/5/2020 | |||||
| milk | 5 | 1/6/2020 | |||||
| milk | 5 | 1/7/2020 | |||||
| milk | 5 | 1/8/2020 | |||||
| milk | 3 | 1/9/2020 | |||||
| milk | 3 | 1/10/2020 | |||||
| eggs | 2 | 1/5/2020 | |||||
| eggs | 2 | 1/6/2020 | |||||
| eggs | 5 | 1/7/2020 | |||||
| eggs | 5 | 1/8/2020 | |||||
| eggs | 5 | 1/9/2020 | |||||
| eggs | 5 | 1/10/2020 |
3 Replies
- kentyler
Solution Sage
It seems a piece is missing
Can there be a forecast for the same item on different dates ?
How is the date range of the "End" table determined ?
It looks like the partial rule is
Starting on the date of the forecast, create a record for every day for that product up to the end of the date range.
Is that a correct interpretation ? 
v-chuncz-msftCommunity Support
