Table modification in script

[teempi]

Hello,

I was wondering if there's some way to modify the contents of a table after it has been loaded. If memory serves, QV9 adds some sort of insertion/update functionality but as I'm using QV8.5 that doesn't help much.

For an example let's say I have a table with only one field. Some of the rows have a null value and I'd like to replace those null values with the previous non-null value. So "1 2 NULL NULL 9 NULL 3" becomes "1 2 2 2 9 9 3" etc.

Is there any way to accomplish this efficiently? Currently I have a solution that creates a new table and inserts the rows one by one in a loop but generating the new rows like this is painfully slow.

Any help is much appreciated.

-Teemu

[#2]

Hi,

Try if this works:

LOAD
Col1,
if(isnull(Col1), previous(Col1), Col1) as Col2
FROM xyz;

Col2 is the new field which will have nulls replaced with previous value in Col1.

Thanks

Amit

[stephencredmond · Accepted solution]

Hi Teemu,

I was going to propose that you might use the Previous statement in a comparison, but then I remembered that Previous uses the source data (which may still have Nulls, so it is better to use Peek to compare against the actual last loaded value.

Something like this work for you:

SET NULLINTERPRET=*;
TempTable:
LOAD * INLINE [
 F1, F2, F3, F4, F5
 Test1, 1, 2, 3, 4
 Test2, 5, *, *, 6
 Test3, *, 7, *, *
 Test4, *, *, 8, 9
 Test5, *, *, 8, 9
 Test6, *, *, 8, 9
 Test7, *, *, 8, 9
 Test8, *, *, 8, 9
];

RealTable:
Noconcatenate Load
 F1,
 If(IsNull(F2), Peek('F2',-1,'RealTable'), F2) As F2,
 If(IsNull(F3), Peek('F3',-1,'RealTable'), F3) As F3,
 If(IsNull(F4), Peek('F4',-1,'RealTable'), F4) As F4,
 If(IsNull(F5), Peek('F5',-1,'RealTable'), F5) As F5
Resident TempTable;
Drop Table TempTable;

Regards,

Stephen

[teempi]

Thanks for the answers! Stephen's one was just what I was looking for. I didn't realize you could also use peek like that on the table you're just loading. This solution seems very handy. Thanks again!

-Teemu