Skip to main content
Announcements
See what Drew Clarke has to say about the Qlik Talend Cloud launch! READ THE BLOG
cancel
Showing results for 
Search instead for 
Did you mean: 
dennysetiawan
Partner - Contributor III
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
maxgro
MVP
MVP

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
sunny_talwar

May be this:

LOAD Date

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

FROM Source

dennysetiawan
Partner - Contributor III
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?

sunny_talwar

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

];

maxgro
MVP
MVP

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