topic Script to search previous data when not null in QlikView

Script to search previous data when not null

dennysetiawan — Wed, 25 Nov 2020 16:16:04 GMT

Dear Community,

I have a table like this:

Date Balance

1/1/2016 1000

1/2/2016 2000

1/3/2016 4000

1/4/2016

1/5/2016

1/6/2016

1/7/2016 2500

1/8/2016 0

As you can see, in 1/4/2016 til 1/6/2016 there is a null value of Balance.

I want to put that null value to be a previous of date which last value of Balance is not null.

So the table should be like this:

Date Balance

1/1/2016 1000

1/2/2016 2000

1/3/2016 4000

1/4/2016 4000

1/5/2016 4000

1/6/2016 4000

1/7/2016 2500

1/8/2016 0

Anyone can help me what is the best script to get a result like that?

Thank you

Re: Script to search previous data when not null

sunny_talwar — Thu, 10 Mar 2016 16:51:45 GMT

May be this:

LOAD Date

If(Len(Trim(Balance)) = 0, Peek('Balance'), Balance) as Balance

FROM Source

Re: Script to search previous data when not null

dennysetiawan — Thu, 10 Mar 2016 17:09:11 GMT

Hi Sunny,

That script only take effect in 1/4/2016, but the next row still null...

Date Balance

1/1/2016 1000

1/2/2016 2000

1/3/2016 4000

1/4/2016 4000

1/5/2016

1/6/2016

1/7/2016 2500

1/8/2016 0

any another suggestion?

Re: Script to search previous data when not null

sunny_talwar — Thu, 10 Mar 2016 17:40:55 GMT

Check this:

Script used:

Table:

LOAD Date,

If(Len(Trim(Balance)) = 0, Peek('Balance'), Balance) as Balance;

LOAD Date,

If(Len(Trim(Balance)) > 0, Balance) as Balance;

LOAD * Inline [

Date, Balance

1/1/2016, 1000

1/2/2016, 2000

1/3/2016, 4000

1/4/2016,

1/5/2016,

1/6/2016,

1/7/2016, 2500

1/8/2016, 0

];

Re: Script to search previous data when not null

maxgro — Thu, 10 Mar 2016 17:50:23 GMT

I would use a peek(Balance) with an order by Date

Table:

LOAD Date,

If(Len(Trim(Balance)) > 0, Balance) as Balance;

LOAD * Inline [

Date, Balance

1/2/2016, 2000

1/3/2016, 4000

1/4/2016,

1/1/2016, 1000

1/5/2016,

1/6/2016,

1/7/2016, 2500

1/8/2016, 0

];

Final:

NoConcatenate LOAD

Date,

alt(Balance, peek('Balance')) as Balance

Resident Table

Order by Date;

DROP Table Table;

Re: Script to search previous data when not null

sunny_talwar — Thu, 10 Mar 2016 17:51:46 GMT

Agreed