Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements
QlikView Fans! We’d love to hear from you.
Share your QlikView feedback with the product team… Click here to participate in our 5-minute survey.
Rules, plus terms and conditions, can be found here.
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

Re: For Statement Optimization

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
8 Replies

Re: For Statement Optimization

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

Re: For Statement Optimization

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

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

Re: Re: For Statement Optimization

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

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


talk is cheap, supply exceeds demand
Not applicable

Re: Re: For Statement Optimization

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

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

Re: For Statement Optimization

Thanks Gysbert, that worked like an absolute charm.

Regards


Rahul

Community Browser