topic Re: Modify PRate to show values in QlikView

Modify PRate to show values

Tue, 24 Jan 2017 15:04:45 GMT

Need help to modify PRate to show Previous date value wherever it is null

Date	Curr	PRate	Modified PRate
01/01/2015	CAD	1,350	1,350
01/31/2015	CAD		1,350
03/01/2015	CAD		1,350
04/01/2015	CAD	1,430	1,430
04/01/2015	CAD		1,430
05/01/2015	CAD		1,430
06/01/2015	CAD		1,430
07/01/2015	CAD		1,430
08/01/2015	CAD		1,430
09/01/2015	CAD		1,430
10/01/2015	CAD		1,430
11/01/2015	CAD		1,430
12/01/2015	CAD		1,430
01/01/2016	CAD		1,430
02/01/2016	CAD		1,430
03/01/2016	CAD		1,430
04/01/2016	CAD	1,430	1,430
04/01/2016	CAD		1,430
05/01/2016	CAD		1,430
05/16/2016	CAD	1,450	1,450
06/01/2016	CAD		1,450
07/01/2016	CAD		1,450
08/01/2016	CAD		1,450
09/01/2016	CAD		1,450
10/01/2016	CAD		1,450
11/01/2016	CAD		1,450
12/01/2016	CAD		1,450
01/01/2017	CAD		1,450
04/01/2017	CAD	1,440	1,440

Thanks in advance.

Re: Modify PRate to show values

MK_QSL — Tue, 24 Jan 2017 15:12:05 GMT

If(IsNull(PRate),Peek('ModifiedRate'),PRate) as ModifiedRate

Re: Modify PRate to show values

sunny_talwar — Tue, 24 Jan 2017 15:14:06 GMT

Try this:

Table:

LOAD * INLINE [

Date, Curr, PRate

01/01/2015, CAD, "1,350"

01/31/2015, CAD

03/01/2015, CAD

04/01/2015, CAD, "1,430"

04/01/2015, CAD

05/01/2015, CAD

06/01/2015, CAD

07/01/2015, CAD

08/01/2015, CAD

09/01/2015, CAD

10/01/2015, CAD

11/01/2015, CAD

12/01/2015, CAD

01/01/2016, CAD

02/01/2016, CAD

03/01/2016, CAD

04/01/2016, CAD, "1,430"

04/01/2016, CAD

05/01/2016, CAD

05/16/2016, CAD, "1,450"

06/01/2016, CAD

07/01/2016, CAD

08/01/2016, CAD

09/01/2016, CAD

10/01/2016, CAD

11/01/2016, CAD

12/01/2016, CAD

01/01/2017, CAD

04/01/2017, CAD, "1,440"

];

FinalTable:

LOAD *,

If(Curr = Previous(Curr) and Len(Trim(PRate)) = 0, Peek('Modified PRate'), PRate) as [Modified PRate]

Resident Table

Order By Curr, Date;

DROP Table Table;

Re: Modify PRate to show values

Tue, 24 Jan 2017 15:36:38 GMT

Thank you so much for your help.

Re: Modify PRate to show values

sunny_talwar — Tue, 24 Jan 2017 15:41:53 GMT

If this is resolved, please close this thread by marking correct and helpful responses...

Qlik Community Tip: Marking Replies as Correct or Helpful

Best,

Sunny