Announcements
See what Drew Clarke has to say about the Qlik Talend Cloud launch! READ THE BLOG
cancel
Showing results for
Did you mean:
Partner - Contributor III

## 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

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

Table:

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

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:

Date,

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

Resident Table

Order by Date;

DROP Table Table;

5 Replies
MVP

May be this:

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

FROM Source

Partner - Contributor III
Author

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?

MVP

Check this:

Script used:

Table:

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

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

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

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

Table:

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

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:

Date,

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

Resident Table

Order by Date;

DROP Table Table;

MVP

Agreed

Community Browser