Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

For Statement Optimization

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

1 Solution

Accepted Solutions
Gysbert_Wassenaar

The problem was in the first LET statement. Add the LOAD keyword to it:

LET vLoad = 'LOAD Key, Sale , VarValue_Time';


talk is cheap, supply exceeds demand

View solution in original post

8 Replies
Gysbert_Wassenaar

Try creating only the load statement in a loop and then use a single load statement to create the needed result. See attached example.


talk is cheap, supply exceeds demand
Not applicable
Author

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

Gysbert_Wassenaar

Surely you didn't simply copy+paste my script into your document? You'll have to adapt it to your situation.


talk is cheap, supply exceeds demand
Not applicable
Author

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;

Gysbert_Wassenaar

len(trim([' & '$(vField)' & ']))=''  should be len(trim([' & '$(vField)' & ']))=0


talk is cheap, supply exceeds demand
Not applicable
Author

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

Gysbert_Wassenaar

The problem was in the first LET statement. Add the LOAD keyword to it:

LET vLoad = 'LOAD Key, Sale , VarValue_Time';


talk is cheap, supply exceeds demand
Not applicable
Author

Thanks Gysbert, that worked like an absolute charm.

Regards


Rahul