Get value from previous record

[nicholas5141]

Hi All, need assists to get value from previous record.

If previous month is 0 then on based latest month value.

I tried previous function but i only mange to fill up previous month value.

Input:

Load * Inline [

Date, Material ,Outlet, Price

31/01/2017 ,a ,O1 ,0

28/02/2017 ,a ,O1 ,0

31/03/2017 ,a ,O1 ,300

30/04/2017 ,a ,O1 ,400

31/01/2017 ,a ,O2 ,0

28/02/2017 ,a ,O2 ,0

31/03/2017 ,a ,O2 ,0

30/04/2017 ,a ,O2 ,444

31/01/2017 ,b ,O1 ,0

28/02/2017 ,b ,O1 ,0

31/03/2017 ,b ,O1 ,0

30/04/2017 ,b ,O1 ,587

31/01/2017 ,b ,O2 ,0

28/02/2017 ,b ,O2 ,300

31/03/2017 ,b ,O2 ,0

30/04/2017 ,b ,O2 ,0

31/01/2017 ,c ,O1 ,0

28/02/2017 ,c ,O1 ,0

31/03/2017 ,c ,O1 ,300

30/04/2017 ,c ,O1 ,0

31/01/2017 ,c ,O2 ,222

28/02/2017 ,c ,O2 ,0

31/03/2017 ,c ,O2 ,0

30/04/2017 ,c ,O2 ,300

];

Expected Output

Date	Material	Outlet	Price	Expected Output
31/01/2017	a	O1	300	300
28/02/2017	a	O1	0	300
31/03/2017	a	O1	300	300
30/04/2017	a	O1	400	400
31/01/2017	a	O2	0	444
28/02/2017	a	O2	0	444
31/03/2017	a	O2	0	444
30/04/2017	a	O2	444	444
31/01/2017	b	O1	0	587
28/02/2017	b	O1	0	587
31/03/2017	b	O1	0	587
30/04/2017	b	O1	587	587
31/01/2017	b	O2	0	300
28/02/2017	b	O2	300	300
31/03/2017	b	O2	0	0
30/04/2017	b	O2	0	0
31/01/2017	c	O1	0	300
28/02/2017	c	O1	0	300
31/03/2017	c	O1	300	300
30/04/2017	c	O1	0	0
31/01/2017	c	O2	222	222
28/02/2017	c	O2	0	300
31/03/2017	c	O2	0	300
30/04/2017	c	O2	300	300

Thanks a lot!

[sunny_talwar · Accepted solution]

Try this

Input:

Load * Inline [

Date, Material ,Outlet, Price

31/01/2017 ,a ,O1 ,0

28/02/2017 ,a ,O1 ,0

31/03/2017 ,a ,O1 ,300

30/04/2017 ,a ,O1 ,400

31/01/2017 ,a ,O2 ,0

28/02/2017 ,a ,O2 ,0

31/03/2017 ,a ,O2 ,0

30/04/2017 ,a ,O2 ,444

31/01/2017 ,b ,O1 ,0

28/02/2017 ,b ,O1 ,0

31/03/2017 ,b ,O1 ,0

30/04/2017 ,b ,O1 ,587

31/01/2017 ,b ,O2 ,0

28/02/2017 ,b ,O2 ,300

31/03/2017 ,b ,O2 ,0

30/04/2017 ,b ,O2 ,0

31/01/2017 ,c ,O1 ,0

28/02/2017 ,c ,O1 ,0

31/03/2017 ,c ,O1 ,300

30/04/2017 ,c ,O1 ,0

31/01/2017 ,c ,O2 ,222

28/02/2017 ,c ,O2 ,0

31/03/2017 ,c ,O2 ,0

30/04/2017 ,c ,O2 ,300

];

Final:

LOAD *,

If(Material = Previous(Material) and Outlet = Previous(Outlet), If(Price = 0, Peek('Expected Output'), Price), Price) as [Expected Output]

Resident Input

Order By Material, Outlet, Date desc;

DROP Table Input;