Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
I have the following scenario.
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.
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.
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 :
@Sowmya_Srinivasan Are you using QlikView or Qlik Sense? I would like to move this into the correct product forum. Thank you.
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;