topic Re: Forward fill the empty rows in QLik in App Development

Forward fill the empty rows in QLik

Qlikuser09 — Thu, 28 Oct 2021 19:55:37 GMT

Is there a way to forward fill the empty cells with the contents from the above cell in the data load editor?

ID	Date	SEX	AGE	Product	Purchased
1	07/30/2012	F	11	Pen	Yes
				Pencil
				Book
2	05/07/2012	M	10	pen	No
3	01/04/2012	F	45	Pen	Yes
				Pencil
				Book
				marker

Re: Forward fill the empty rows in QLik

stevejoyce — Thu, 28 Oct 2021 21:00:23 GMT

You can use the peek function in load script to do this. If you dont want to interpret blank as null, you can use if statement with '' or len = 0 instead of nullinterpret + coalesce

NullInterpret = '';

source_data:
load
coalesce(ID, peek(ID)) as ID
,coalesce(Date, peek(Date)) as Date
,coalesce(SEX, peek(SEX)) as SEX
,coalesce(AGE, peek(AGE)) as AGE
,coalesce(Product, peek(Product)) as Product
,coalesce(Purchased, peek(Purchased)) as Purchased

from https://community.qlik.com/t5/App-Development/Forward-fill-the-empty-rows-in-QLik/td-p/1853296

(html, utf8, embedded labels, table is @1)
;

exit script;

Re: Forward fill the empty rows in QLik

Qlikuser09 — Thu, 28 Oct 2021 23:55:48 GMT

Hi, only the Account id gets filled in the below column, however rest of the column are still null

ID	Date	SEX	AGE	PROTECTIVE DEVICES	Purchased
1	07/30/2012	F	11	Pen	Yes
1	-	-	-	Pencil	-
1	-	-	-	Book	-
2	05/07/2012	M	10	pen	No
3	01/04/2012	F	45	Pen	Yes
3	-	-	-	Pencil	-
3	-	-	-	Book	-
3	-	-	-	marker	-

Re: Forward fill the empty rows in QLik

stevejoyce — Sat, 30 Oct 2021 19:01:18 GMT

Can you share your script you used. Otherwise then it must not be recognizing your empty rows as null.

You can try

,if(len(trim(Date))>0,Date, peek(Date)) as Date

repeat for each column. see if that helps.