Copy last value instead of fill wizard.

[#1]

I have table like this:

account	Date	Value
1	2010-01-01	0
1	2010-01-02	NULL
1	2010-01-03	NULL
1	2010-01-04	NULL
1	2010-01-05	NULL
1	2010-01-06	NULL
1	2010-01-07	39,62
1	2010-01-08	NULL
1	2010-01-09	NULL
1	2010-01-10	NULL
1	2010-01-11	12
1	2010-01-12	NULL
1	2010-01-13	NULL
2	2010-01-01	0
2	2010-01-02	NULL
2	2010-01-03	NULL
2	2010-01-04	NULL
2	2010-01-05	NULL
2	2010-01-06	5
2	2010-01-07	NULL
2	2010-01-08	NULL
2	2010-01-09	NULL
2	2010-01-10	NULL
2	2010-01-11	6
2	2010-01-12	NULL
2	2010-01-13	NULL
3	2010-01-01	0
3	2010-01-02	1
3	2010-01-03	NULL
3	2010-01-04	NULL
3	2010-01-05	NULL
3	2010-01-06	NULL
3	2010-01-07	NULL
3	2010-01-08	NULL
3	2010-01-09	NULL
3	2010-01-10	NULL
3	2010-01-11	2
3	2010-01-12	NULL
3	2010-01-13	NULL

And would like to recived like this:

account	Date	Value
1	2010-01-01	0
1	2010-01-02	0
1	2010-01-03	0
1	2010-01-04	0
1	2010-01-05	0
1	2010-01-06	0
1	2010-01-07	39,62
1	2010-01-08	39,62
1	2010-01-09	39,62
1	2010-01-10	39,62
1	2010-01-11	12
1	2010-01-12	12
1	2010-01-13	12
2	2010-01-01	0
2	2010-01-02	0
2	2010-01-03	0
2	2010-01-04	0
2	2010-01-05	0
2	2010-01-06	5
2	2010-01-07	5
2	2010-01-08	5
2	2010-01-09	5
2	2010-01-10	5
2	2010-01-11	6
2	2010-01-12	6
2	2010-01-13	6
3	2010-01-01	0
3	2010-01-02	1
3	2010-01-03	1
3	2010-01-04	1
3	2010-01-05	1
3	2010-01-06	1
3	2010-01-07	1
3	2010-01-08	1
3	2010-01-09	1
3	2010-01-10	1
3	2010-01-11	2
3	2010-01-12	2
3	2010-01-13	2

I want to replace all nulls with last amount. I know i can do it by Input file table wizard with "fill" option (above) but I don want to use it becouse this table is result of previous operation, and i should not store it. Anyone ?

[renjithpl]

Try,

if(Value='NULL', 0, Value) as Value

[#3]

Thanks for response.

I want to put instead of Null last value not 0 what you suggested.

Any ideas?

[#4 · Accepted solution]

Hi,
try this instead:

if(Value='NULL', peek(Value), Value) as Value

or

if(Value='NULL', previous(Value), Value) as Value

These functions should do about the same thing, please check help content for further syntax.

Good luck,

Jonas

www.optivasys.com

[googel84]

I never tried the function I'm suggesting you but, taking a look to re reference manual, I think it could be the right choice.

LOAD
account,
Date,
if (isNull(Value) = -1, previous(Value),Value)
FROM ...

[#6]

Thanks a lot, previous() doesn't work in this case (copy only one value don't copy value from previous if() iteration), but peek seems to be working 🙂