topic Re: Cleanup in script - set variables to NULL in QlikView

Cleanup in script - set variables to NULL

datanibbler — Thu, 05 Feb 2015 13:06:01 GMT

Hi,

in principle I am already doing this: Every variable that is being used in a script is afterwards set to NULL so that I won't eventually end up with obsolete variables that can cause confusion in an app.

I have now a piece of code that loads an Excel_file with the two logons of the developers we have and creates out of those a visibility_condition like

>> OSUSER() = 'SYNCREON\name.surname OR OSUSER() = 'SYNCREON\name2.surname2' <<

Pretty complex stuff. It's basically all being done in a loop which I will post here.

The challenge is: I already do set the variables to NULL, but I still have the evaluations of the variables left when I run that.

=> How can I set those to NULL?

( >> SET $(varname) = ; << (inside the loop) doesn't work, that would be easy ... )

This is the loop (the Excel_file is attached)

FOR i = 1 TO $(v_admins) // that is the nr. of rows
LET varname = 'v_admin' & $(i);
LET $(varname) = PEEK('OSU', ($(i)-1), Developers_pre); //Developers_pre is the table that I first get when I load the file
LET varname2 = 'e_admins_only_part' & $(i);
LET $(varname2) = 'OSUSER() = ' & $(varname);
// I have exclamation_marks around the individual elements here to avoid confusion with upper_quotes (they are replaced lateron)

LET e_admins_only = '$(e_admins_only)' & ' OR OSUSER() = ' & chr(33) & $(varname) & chr(33);
// The variables are now all deleted (set to NULL)

SET varname2 = ;
SET varname = ;
NEXT i;

Can anybody help me here?

Thanks a lot!

Best regards,

DataNibbler

Re: Cleanup in script - set variables to NULL

marcus_sommer — Thu, 05 Feb 2015 13:32:38 GMT

Hi DataNibbler,

I'm not sure if this approach would work but you could try it:

Create within your loop a delete-statement for these unwanted created variable:

....

Let vDeletevariable = 'LET ' & YourVariableValue & '= null();';

$(vDeletevariable)

....

Maybe this link is a kind of explanation: http://community.qlik.com/message/176351#176351

- Marcus

Re: Cleanup in script - set variables to NULL

datanibbler — Thu, 05 Feb 2015 13:35:15 GMT

Hi Marcus,

I cannot open that link, I am not authorized, for whatever reason.

I will try your proposal. Looks good.

Re: Cleanup in script - set variables to NULL

datanibbler — Thu, 05 Feb 2015 13:38:20 GMT

... and it works. The editor apparently doesn't know it, the statement is underlined (at least in my outdated version of the Client), but it runs and works - the variable is apparently gone.

Thanks a lot!

Best regards,

DataNibbler

Re: Cleanup in script - set variables to NULL

Colin-Albert — Thu, 05 Feb 2015 13:39:10 GMT

Hi DataNibbler,

The link Marcus posted is to a post in the QlikBug group.

Click Places on the toolbar and you can join the group from the list.

Re: Cleanup in script - set variables to NULL

Thu, 05 Feb 2015 13:49:32 GMT

Hi Data,

I know

SET $(varname) = ;

won't work for you but

SET v_admin$(i) =;

SET e_admins_only_part$(i)=;

should do I think. Maybe give that a try

hope that helps

Joe

Re: Cleanup in script - set variables to NULL

peterwh — Thu, 05 Feb 2015 13:49:48 GMT

Hello,

I see thats there's already an answer, but I have a simple remark to your script. You are using "SET varname2 = ;"

I clean all my variables by "LET varname2 = NULL();". With that it doesn't appear in the Variable Overview. So if you have the same varname you can simply use this. The solution of Marcus Sommer is more sophisticated or comes this from the Bug-Thread?

Kind regards

Peter

Re: Cleanup in script - set variables to NULL

datanibbler — Thu, 05 Feb 2015 14:02:38 GMT

Hi Peter,

I can incorporate that into Marcus' answer - or edit it in that way.

That looks even better.

I cannot be sure, but I would guess that with your variation, the variables don't exist at all anymore and thus do not take any space whereas with my method, the variables still exist, albeit with no value assigned.

Can anybody confirm whether this is right?

Thanks a lot!

Best regards,

DataNibbler

Re: Cleanup in script - set variables to NULL

Anonymous — Mon, 16 Mar 2015 21:42:13 GMT

I have this exact same question, DataNibbler.

Any answer to this one yet? If not, I'll re-post as a separate topic to the community.

I'm looking towards performance: how does SET = ; compare to LET = NULL(): in terms of performance? Or is it so negligible that it is insignificant?

Re: Cleanup in script - set variables to NULL

marcus_sommer — Tue, 17 Mar 2015 06:03:07 GMT

From a performance point of view there will be none big difference between set v =; or let v = null(); I think the engine will be able to execute thousands from such statements within a second .... maybe by one a few more ...

- Marcus

Re: Cleanup in script - set variables to NULL

Anonymous — Tue, 17 Mar 2015 15:01:53 GMT

Thanks for the answer, Marcus.

In the end, I'm opting to use SET v = ;

1. It empties the variable so the value isn't stored (which is the point)

2. It retains the variable for the next run so it doesn't have to be created again

Neither of these are likely to be noticeable as variable allocation/deallocation is not system-intensive. It's more about best practices and deciding the best way to build something for future re-use.

As a side note, if I had a large, generic clean-up script that resets many (1000's) of the same variables across multiple scripts, I might opt for LET v = NULL(); instead. This is so the SET statements don't create needless variables that linger in applications where they are not used. But this is not a best-practice, either.