Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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.
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
If(IsNull(PRate),Peek('ModifiedRate'),PRate) as ModifiedRate
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;
Thank you so much for your help.
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