Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

Highlighted
Partner
Partner

Script to search previous data when not null

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

1 Solution

Accepted Solutions
MVP
MVP

Re: Script to search previous data when not null

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;

View solution in original post

5 Replies

Re: Script to search previous data when not null

May be this:

LOAD Date

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

FROM Source

Partner
Partner

Re: Script to search previous data when not null

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

Check this:

Capture.PNG

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

];

MVP
MVP

Re: Script to search previous data when not null

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;

View solution in original post

Re: Script to search previous data when not null

Agreed