topic Re: For Statement Optimization in QlikView

For Statement Optimization

Fri, 25 Apr 2014 08:35:40 GMT

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

Re: For Statement Optimization

Gysbert_Wassenaar — Fri, 25 Apr 2014 12:34:39 GMT

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

Re: For Statement Optimization

Fri, 25 Apr 2014 15:30:42 GMT

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

Re: For Statement Optimization

Gysbert_Wassenaar — Fri, 25 Apr 2014 15:37:21 GMT

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

Re: Re: For Statement Optimization

Fri, 25 Apr 2014 16:09:17 GMT

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;

Re: For Statement Optimization

Gysbert_Wassenaar — Fri, 25 Apr 2014 16:16:03 GMT

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

Re: Re: For Statement Optimization

Mon, 28 Apr 2014 12:12:02 GMT

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

Re: For Statement Optimization

Gysbert_Wassenaar — Mon, 28 Apr 2014 16:48:51 GMT

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

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

Re: For Statement Optimization

Mon, 28 Apr 2014 17:37:31 GMT

Thanks Gysbert, that worked like an absolute charm.

Regards

Rahul