Qlik Community

Community Corner

Discussion board for questions about the Qlik Community, its features, sharing information, general discussions and even some fun. This is for both new and longtime community members. Everyone is welcome!

Announcements
Coming your way, the Qlik Data Revolution Virtual Summit. October 27-29. REGISTER
cancel
Showing results for 
Search instead for 
Did you mean: 
Contributor
Contributor

Replace null/Zero values with previous value

I have the following scenario.

Sowmya_Srinivasan_0-1595571104446.png

I have year month and score as two columns. For certain year month, the score values are either zero or null. When the score value is zero or null for a particular year month i am expected to get the previous score value greater than zero. 

Example: For 2020008 and 2020009 the score values are zero i am expected to fill the rows with 90 which is the previous score value. I need the resultant table to look like this.

Sowmya_Srinivasan_1-1595571386667.png

First two records should be zero because the scoring started only from 2020003.

Note: I need to do the changes at the back end.

Thanks in advance.

 

Labels (3)
3 Replies
Highlighted
Master
Master

Maye be this :

load Date,if(rowno()=1,0,if(IsNull(Score),peek(Score),Score)) as Score ;
load Date,if(Score <>0 and len(trim(Score))>0,Score) as Score   inline [
Date,Score
202001,
202002,0
202003,83
202004,0
202005,0
202006,
202007,90
202008,0
202009,0
2020010,92
];

 

output :

Capture.PNG

Regards,
Taoufiq ZARRA

"Please LIKE posts and "Accept as Solution" if the provided solution is helpful "

(you can mark up to 3 "solutions") 😉
Highlighted
Community Manager
Community Manager

@Sowmya_Srinivasan Are you using QlikView or Qlik Sense? I would like to move this into the correct product forum. Thank you. 

Sue Macaluso
Highlighted
MVP
MVP

Date format looks weird but try to convert it in proper format. Because you first need to order your data otherwise peek and previous logic won't work properly. Try below

Data:
LOAD date#(Left(Date,4)&right(Date,2),'YYYYMM') as Date,Score;
load * inline [
Date,Score
2020001,
2020002,0
2020003,83
2020004,0
2020005,0
2020006,
2020007,90
2020008,0
2020009,0
2020010,92
];

New:
NoConcatenate
LOAD Date,
     rangesum(if(Score>0, Score,if((len(trim(Score))=0 or Score=0) and Peek(Score)>0,Peek(Score),Peek(Score)))) as Score
Resident Data
Order by Date;

DROP Table Data;