Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi everybody ,
I need your help :
I have a table with those columns : Year/Month/ProductID/Amount
My problem is , for the actual month the column amount is always 0 , I'll like to create a new column who return the last amount known for a product.
For example :
Year | Month | ProductID | Amount |
---|---|---|---|
2016 | 10 | 1 | 10 |
2016 | 11 | 1 | 12 |
2016 | 12 | 1 | 0 |
2014 | 10 | 2 | 25 |
2016 | 1 | 3 | 30 |
2016 | 12 | 3 | 0 |
2016 | 12 | 2 | 0 |
result expected :
Year | Month | ProductID | Amount | LastAmount |
---|---|---|---|---|
2016 | 10 | 1 | 10 | 10 |
2016 | 11 | 1 | 12 | 12 |
2016 | 12 | 1 | 0 | 12 |
2014 | 10 | 2 | 25 | 25 |
2016 | 1 | 3 | 30 | 30 |
2016 | 12 | 3 | 0 | 30 |
2016 | 12 | 2 | 0 | 25 |
someone have an idea ?
Try this:
Table:
LOAD * INLINE [
Year, Month, ProductID, Amount
2016, 10, 1, 10
2016, 11, 1, 12
2016, 12, 1, 0
2014, 10, 2, 25
2016, 1, 3, 30
2016, 12, 3, 0
2016, 12, 2, 0
];
FinalTable:
LOAD *,
If(ProductID = Previous(ProductID) and Amount = 0, Peek('Amount'), Amount) as LastAmount
Resident Table
Order By ProductID, Year, Month;
DROP Table Table;