topic Re: Fill with last non null value during script load in QlikView

Fill with last non null value during script load

Sun, 10 May 2015 18:45:45 GMT

Hi,

I've got the following situation where there's this table (blue-colored columns):

NOTE: The green colored column is NOT part of the input data, it should be the output of the process I'm trying to figure out.

Product	Period	Price	Desired Output: Price Column
A	2015-01	2	2
A	2015-02		2
A	2015-03		2
A	2015-04	3	3
B	2015-03		0
B	2015-04	2	2
B	2015-05		2
B	2015-06		2
C	2015-01		0
C	2015-02		0
C	2015-03	4	4
C	2015-04		4

GOAL: What I want to achieve during the script load is to replicate the last non null price value in all the following null price fields (that is unless there is a new price value).

An example of what the desired output should be is shown in the green column (Desired Output).

Thank you all in advance.

Re: Fill with last non null value during script load

maxgro — Sun, 10 May 2015 19:43:23 GMT

LOAD Product, Period, Price

FROM

[https://community.qlik.com/thread/163391]

(html, codepage is 1252, embedded labels, table is @1);

load *,

if(len(trim(Price))=0 and Product=peek(Product), Peek(DesiredPrice), alt(Price,0)) as DesiredPrice

Resident t

order by Product, Period;

DROP Table t;

Re: Fill with last non null value during script load

Sun, 10 May 2015 20:21:54 GMT

Massimo, thanks for you reply.

The Desired Price Column is not included in the input data, it should be the result of the script calculations.

Sorry if it is not clear.

Re: Fill with last non null value during script load

swuehl — Sun, 10 May 2015 20:55:20 GMT

Massimo is not using the Desired Price Column when loading your sample table (loaded from this web page), but creating this column in his f table script.

Isn't this fulfilling your requirements?

Re: Fill with last non null value during script load

Sun, 10 May 2015 23:15:18 GMT

Oh, my bad. It does.

Thank you all.