Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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;
May be this:
LOAD Date
If(Len(Trim(Balance)) = 0, Peek('Balance'), Balance) as Balance
FROM Source
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?
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
];
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;
Agreed