I would have to distribute in rows each group of 5 fields, it is a qvd of several million records, for now I have it done with a loop but it has to read 30 times and it takes a lot. Do you know if there is a way to avoid reading it 30 times?
FOR i = 1 to 30
LET vNum = Num ($(i), '00');
Field1$(vNum) as Field1,
Field2$(vNum) as Field2,
Field3$(vNum) as Field3,
Field4$(vNum) as Field4,
Field5$(vNum) as Field5
Field1$(vNum) <> 0
I'm not looking for delta/incremental loads solutions.
Sorry, I didn't notice that it was really 30 iterations and not just 30 columns. In this case my suggestion would be also need 30 iterations.
Beside them I don't believe that there are really faster solutions than such kind of loop. Of course there are solutions with crosstable thinkable but they are quite performance intense by millions of records so that a optimized qvd-load should be faster.
The only option to keep it optimized is to remove the where-clause (and applying it within a following resident-load) or a where exists() with only one parameter.
Beside this if you could change or maybe extend the logic which creates these qvd you might be able to skip this task.
One thing that comes to mind is to do a singe load with some RangeSum() expressions. Something like
RangeSum(Field101, Field102, Field103, ... Field130) as Field1,
RangeSum(Field201, Field202, Field203, ... Field230) as Field2,
RangeSum(Field501, Field502, Field503, ... Field530) as Field5
FROM [$(vPath)FileName.qvd] (qvd);
However, this does aggregate the values. But as you cannot separate those values anyway (you have no way of separating the values of 101 and 102 from the same line of source once loaded into Field1), this may be an advantage.
Logic will get you from a to b. Imagination will take you everywhere. - A Einstein