Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Good Morning All,
I have a QV document that uses a for loop to find a peak of all fields. I have more that 200 fields and am using the following script:
Let vcount =NoOfFields('NewData')-3;
trace $(vcount);
FOR i=4 to 3+$(vcount)
Let vname = '"' & FieldName(i ,'NewData') & '"';
trace $(vname);
if (i=4) then
Test2:
LOAD
Key, Sale, VarValue_Time,
If(len(trim($(vname)))=0 and Key = Previous(Key), Peek($(vname)), $(vname)) As $(vname)
Resident NewData Order by Key, Sale, VarValue_Time;
ELSE
join(Test2)
LOAD Key, Sale, VarValue_Time,
If(len(trim($(vname)))=0 and Key = Previous(Key), Peek($(vname)), $(vname)) As $(vname)
Resident NewData Order by Key, Sale, VarValue_Time;
ENDIF
NEXT;
The script does exactly what I need it to do and does the job perfectly in the QVW attached, however when I run it on many days worth of data it runs out of memory.
Is there any way to optimize this script so that it does not run out of memory?
Any help would be hugely appreciated.
Regards Rahul
The problem was in the first LET statement. Add the LOAD keyword to it:
LET vLoad = 'LOAD Key, Sale , VarValue_Time';
Try creating only the load statement in a loop and then use a single load statement to create the needed result. See attached example.
Dear Gysbert,
Thanks for that, however that lead me to a un know statement error
I should have mentioned, I have more than a million lines of data in the table I am using.
Regards
Rahul
Surely you didn't simply copy+paste my script into your document? You'll have to adapt it to your situation.
no I did not. My script below: LET vLoad = 'Key, Sale , VarValue_Time'; for i = 4 to NoOfFields('NewData') LET vField = FieldName(i,'NewData'); LET vLoad = '$(vLoad)' & ', if(Key = previous(Key) and len(trim([' & '$(vField)' & ']))='', peek(' & chr(39) & '$(vField)' & chr(39) & '),[' & '$(vField)' & ']) as [' & '$(vField)' & ']' ; next LET vLoad = '$(vLoad)' & 'resident NewData;'; Test2: NoConcatenate $(vLoad); drop table NewData;
len(trim([' & '$(vField)' & ']))='' should be len(trim([' & '$(vField)' & ']))=0
Dear Gysbert, How are you? I hope you had a great weekend! I made the change you recommended however it still come back with the same error. Please see the test file attached. Regards Rahul
The problem was in the first LET statement. Add the LOAD keyword to it:
LET vLoad = 'LOAD Key, Sale , VarValue_Time';
Thanks Gysbert, that worked like an absolute charm.
Regards
Rahul