How to retrieve the last known information in a table ?

[#1]

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 ?

[sunny_talwar]

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;