Summary: QV Version 12 removes  per qvw control of the script EXECUTE Statement.  In QV12 Server, the default is to disallow all EXECUTE statements.  Any EXECUTE statement will fail unless you turn on the global setting to allow.

The QlikView  script “EXECUTE” statement provides the capability to run external programs from script.  Because EXECUTE may present a potential security risk, it’s use must be authorized. QlikView Version 12 changes how EXECUTE authorization is granted.

In QV11  Desktop,  the script editor Settings pane provides a checkbox labeled “Can Execute External Programs”. This property is set per qvw.

When an EXECUTE statement is encountered,  execution is allowed if the property is checked.  If not checked, a popup is presented asking for permission.

The “User Preference: Security: Always Override Security:  Script” may be checked on to bypass the Execute permission check.

If “Script” is checked, the execute statement will be allowed regardless of the “Can Execute External Programs” setting.

Authorizing Execute works differently in QV11 Server reloads.  In QV11 Server prior to SR11,  Execute statements were always allowed. The “Can Execute External Programs” property has no effect.

QV11 SR11+ Server introduced a new setting

"AllowExecuteCommand=0|1".

Setting the value to “0” prohibits Execute in any reload on the server, setting  to “1” allows all Execute statements. The default is “1”, allow.

The “AllowExecuteCommand” is set in the QVB settings file “C:\Windows\System32\config\systemprofile\AppData\Roaming\QlikTech\QlikViewBatch\settings.ini”.   See the SR11 or QV12 Release Notes for more information.

To summarize QV11 controls —  In desktop, it is possible to control Execute on a qvw by qvw basis. In Server SR10 and earlier, Execute is always allowed.  In SR11+, Execute may be allowed or disallowed for all qvws via the “AllowExecuteCommand” setting.

On to QV12.  In QV12 IR Desktop, the “Can Execute External Programs” checkbox is present but it has no impact on script execution.   The only way to allow Execute is to check on the “Always Override Security:  Script” User Preference setting. 

I hope the “Can Execute External Programs” checkbox is removed in a future SR as it no longer seems to have any use.

QV12 Server uses the “AllowExecuteCommand” ini setting. The default is “0”, disallow, which is different than QV11.

To summarize QV12 controls — In Desktop, Execute is allowed or disallowed for all qvws via the “Always Override Security:  Script” User Preference setting. In Server, Execute is allowed or disallowed for all qvws via the “”AllowExecuteCommand” setting.

A summary of controls for both versions:

To allow Execute in Qlik Sense, you must enable Legacy mode. Turning on  Legacy mode also allows  file path references in LOAD statements.  I’m guessing most Sense users would not accept this side effect, which may mean that Execute is not practical in the Sense environment.

Execute is not a commonly used script statement. If you are using Execute, I hope this post helps you plan for QV12.

-Rob

Here’s a heads up on a QlikView script syntax issue.  The problem came up several times in a beginner class last week and I’ve also noted beginners on the QlikCommunity Forum struggling with this as well.

There should be a space after a script keyword, separating it from the next word, right?

Resident  MyTab    // correct
ResidentMyTab     // incorrect

If we omit the space  the syntax coloring will alert us and we may get a red error underline as well.

What if a bracket is used, such as the case when a name contains a space?  If a bracket appears immediately after the keyword, the syntax highlighter will make look it correct. But it is invalid syntax and will fail  when reloaded.

There are a number of places in script where this problem can happen. Here are a few examples.

- LOAD * INLINE[
- [F1] as[F2]
- LOAD[F1]

Good news! QlikView Version 12 corrects the problem. In QV12, the missing space will be flagged in the editor.

While the requirement for a space may seem obvious to an experienced developer or programmer type, it can be a problem for beginners who are copying an example from a book where the space is not so clear, and who rely on highlighting to tell them they have it right.

It can be a difficult error to debug. The resulting script error message can be something  indirect like “missing/misplaced FROM..” when the actual error is a missing space after an “AS” keyword.

I’m going to add the following slide to all my beginner trainings:

Words in expressions or script must be separated by a delimiter. Depending on context, the delimiter will be one of:

Space  Comma  (  {  <  = +  -  *   / Semicolon

[ Single-quote and Double-quote are not delimiters.

I know this incomplete, leaving off tabs, newline, =>. Just keeping it simple.

-Rob

On 2 Oct, I was pleased to join a group of eight Qlik Luminaries on a field trip (we had a bus!) to Qlik R&D in Lund, Sweden.  Ralf Becher has written a nice overview of the trip here.

One outcome of our meeting is a list of enhancements we believe would be valuable additions to QlikView and Qlik Sense.  You can view the entire working list here.

The list includes quite a few topics.  Today I want to focus on improvements to Variables.

A common challenge is”script only” variables getting inadvertently promoted to the UI.  There is a desire to better control and identify variables in the script.

What follows are a number of specific feature improvements for variables raised during the meeting.  The general theme is to extend the current management functions for Fields to Variables as well.

•  A HidePrefix and $hidden Tag for Variables.  Hidden variables would not appear in the Variable overview or Expression Editor dropdown unless “Show System Variables” was checked.

• A “DROP VARIABLES vg*” statement.  The DROP VARIABLE statement would delete a list of variables from both the script and UI. Script deletion of a UI variable is not possible today. Importantly, the list can include wildcards.

• Script functions that provide access to  existing variables.  Similar to functions currently available for Fields, the functions would be:
  • NoOfVariables()
  • VariableName(nbr)

• COMMENT VARIABLE varname WITH ‘comment’
  COMMENT VARIABLES USING mapname
  Currently the only way to add comments to variables is through the Variable Overview dialog or the VBScript API.  Comments are a useful feature and it would be great if comments could be set using script statements.

What do you think?  Would you use these additions?  Are there additional improvements you would suggest for Variables?

-Rob

Every software product has it’s bits of tribal lore, those unintuitive quirks that when revealed make you say “aha!” and feel empowered to mentor others.

A QlikView factoid of this variety is that: “General Script Error” from  a script STORE  statement usually means that the target directory does not exist.  For example, a “General Script Error” from the following statement  if “somedir” does not exist:

STORE mytab INTO somedir\mytab.qvd;

it could actually be any type of output file error. Could be missing directory, could be  locked file or an illegal file name, You know this. You’ve been using QlikView for some time.

I include this tidbit in my beginner classes, and the students are usually grateful to be receiving these “inside tips” .  But occasionally, I get a Programmer type in the class who slowly raises one eyebrow Spock fashion and asks “Why doesn’t it throw a ‘Directory not found’ message'”?  I’ve never been able to give a satisfactory answer to that question,  (This is usually when I announce lunch).

Someone pointed out to me recently that:

SET ErrorMode=0;

Which is supposed to allow Script to continue after errors, does not affect  STORE output file errors. That is, the script still fails with “General Script Error”. This is because the STORE file error is Uncaught/Unhandled.  You’ll recognize this construct if you’ve done any programming.

It’s not like Script can’t catch IO errors. For example, input errors are handled just as you would expect. These statements won’t cause the script to stop even though “foo.bar” does not exist:

SET ErrorMode=0;
 LOAD X FROM foo.bar;

Most of QlikView — both script & UI — fails and recovers extremely gracefully. I don’t know why STORE seems to have been left out in the cold.

-Rob

P.S. I’ll be at Qonnections next week. Let’s see how many of us can ask “Why doesn’t STORE catch output file errors?” at the R&D Q-Bar. J

A customer asked me today if there was a QVW property for an application description that could be pulled as metadata by his scanner app.

I’ve found that using Variables is a very simple way to define additional metadata.

[qlikview]SET vAppDescription = This is the main Sales app. blah blah;[/qlikview]

A different script can load that variable with this bit of script:

[qlikview]Meta:
LOAD [RawValue] as AppDescription
FROM [some qvw path.qvw]
(XmlSimple, Table is [DocumentSummary/VariableDescription]) WHERE Name = ‘vAppDescription’ ;[/qlikview]

-Rob

Join me at the San Francisco Masters Summit for QikView for more scripting tips.

I love the preceding load feature of Qlikview scripting. It can make complex things simple.

Consider for example, having to parse an arbitrary number of key:value pairs from input like:

Name:Shoes, Size:L, Color:Blue Suede, Stock: 200
Name:Socks, Model:Mens Casual, Stock:0, Color:Black
Name:Pants, Error: No attributes found

We want each key to become a Field populated with it’s matching value. Here’s the entire script to do just that.  A preceding load is a series of chained LOADs that execute from the bottom up — so read the script from the bottom.

And now we can build a chart like this.

Simple. Elegant.

I’ll be showing some other cool examples of Preceding Load in my “Advanced Scripting” session at the Masters Summit for Qlikview Oct 1.

-Rob

Script suitable for copy/paste

data:
// 4. Generic Load to transpose Key to Field
Generic LOAD RecId, Key, Value
;
// 3. Separate key & value
LOAD
RecId,
subfield(Pair,':',1) as Key,
subfield(Pair,':',2) as Value
;
// 2. Break out each key:value pair
LOAD
RecId,
subfield(Input,',') as Pair
;
// 1. Load the raw Input
LOAD *, RecNo() as RecId INLINE [
Input
Name:Shoes, Size:L, Color:Blue Suede, Stock: 200
Name:Socks, Model:Mens Casual, Stock:0, Color:Black
Name:Pants, Error: No attributes found
] (delimiter is '|')
;