topic Re: Replace null/Zero values with previous value in Water Cooler

Replace null/Zero values with previous value

Sowmya_Srinivasan — Fri, 24 Jul 2020 06:19:57 GMT

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.

Re: Replace null/Zero values with previous value

Taoufiq_Zarra — Fri, 24 Jul 2020 08:27:52 GMT

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 :

Re: Replace null/Zero values with previous value

Sue_Macaluso — Tue, 28 Jul 2020 18:58:07 GMT

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

Re: Replace null/Zero values with previous value

Kushal_Chawda — Tue, 28 Jul 2020 21:58:53 GMT

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;