Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hello everyone,
I have a table that looks like this :
XXX Status_before Status_after Timestamp
A lala lili 01/01/2015 03:00:00
A - - 01/01/2015 04:00:00
A - - 01/01/2015 05:00:00
A lili lulu 01/01/2015 05:15:34
B xaxa xixi 01/01/2015 03:43:35
I would like that the "-" (that represent null values) to be filled with the previous value of status_after (the table is already sorted).
In that case, I would like the empty lines to be filled with "lili".
It has to be done only if the value of "XXX" is the same, that means if I have
B xaxa xixi 01/01/2015 03:43:35
C - - 01/01/2015 02:00:00
C - - 01/01/2015 03:00:00
C lala lili 01/01/2015 03:23:45
The 2 lines with empty values have to stay like this.
I hope I am clear enough.
Thank you for your help.
Have a good day 
Laura
Hi,
one solution could be:
LOAD XXX,
If(Len(Status_before) or XXX<>Previous(XXX),Status_before,Peek(Status_after)) as Status_before,
If(Len(Status_after) or XXX<>Previous(XXX),Status_after,Peek(Status_after)) as Status_after,
Timestamp
INLINE [
XXX, Status_before, Status_after, Timestamp
A, lala, lili, 01/01/2015 03:00:00
A,,, 01/01/2015 04:00:00
A,,, 01/01/2015 05:00:00
A, lili, lulu, 01/01/2015 05:15:34
B, xaxa, xixi, 01/01/2015 03:43:35
];
hope this helps
regards
Marco
Hi everyone,
there must be something wrong.
I have tried (just to see if at least this would work) :
LOAD *,
peek(Status_before) as Status_before2
resident T2;
And I get :
XXX Status_before Status_after Timestamp Status_before2
A lala lili 01/01/2015 03:00:00 -
A - - 01/01/2015 04:00:00 lala
A - - 01/01/2015 05:00:00 -
A lili lulu 01/01/2015 05:15:34 -
B xaxa xixi 01/01/2015 03:43:35 lili
The peek function give me the previous value but if the previous value is null, I do get a null instead of getting the last not null value 
Thank you Joe !!
I played a bit with your application and now I understand better how peek works !
I was able to make my application work
Have a good day