Qlik Community

Ask a Question

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
On May 18th at 10AM EDT we will answer your QlikView questions live. REGISTER
cancel
Showing results for 
Search instead for 
Did you mean: 
nicholas5141
Partner
Partner

Get value from previous record

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

  

DateMaterialOutletPriceExpected Output
31/01/2017aO1300300
28/02/2017aO10300
31/03/2017aO1300300
30/04/2017aO1400400
31/01/2017aO20444
28/02/2017aO20444
31/03/2017aO20444
30/04/2017aO2444444
31/01/2017bO10587
28/02/2017bO10587
31/03/2017bO10587
30/04/2017bO1587587
31/01/2017bO20300
28/02/2017bO2300300
31/03/2017bO200
30/04/2017bO200
31/01/2017cO10300
28/02/2017cO10300
31/03/2017cO1300300
30/04/2017cO100
31/01/2017cO2222222
28/02/2017cO20300
31/03/2017cO20300
30/04/2017cO2300300

Thanks a lot!

1 Solution

Accepted Solutions
sunny_talwar

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;

View solution in original post

1 Reply
sunny_talwar

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;

View solution in original post