Script to search previous data when not null

[dennysetiawan]

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

[sunny_talwar]

May be this:

LOAD Date

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

FROM Source

[dennysetiawan]

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?

[sunny_talwar]

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

];

[maxgro · Accepted solution]

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;

[sunny_talwar]

Agreed