Category Archives: Scripting

QV 12.10 “Can Execute External Programs” is back

In an earlier post I wrote about how to authorize the script Execute statement and the differences between QlikView versions 11 and 12.  I’ve just tested the new QlikView v12.10 Initial Release and the rules have changed again.

To run an Execute statement in QV12 Desktop requires that “User Preference, Security,  Script (Allow Database Write and Execute Statements)” be checked on.  This is also true in QV12.10.

An additional Script Setting “Can Execute External Programs” is present in QV12 but had no effect.  In QV12.10, this setting behaves as it does in QV11. If off, you will be asked to authorize when the Execute statement is encountered in the script.

For an Execute to run in QV12.10 Desktop without prompting, both “Script (Allow Database Write and Execute Statements)” and “Can Execute External Programs” must be on.

What about QV12.10 Server? Like QV12 Server, Execute is authorized by the setting:

AllowExecuteCommand=1

The value is set in “C:\Windows\System32\config\systemprofile\AppData\Roaming\QlikTech\QlikViewBatch\settings.ini”, and it is off by default after install.

Does QV12.10 Server need the  “Can Execute External Programs” setting? No. As a matter of fact, if it’s off, the reload sets it on. I don’t mean temporarily. I mean it sets and saves it in the qvw.

Updating the matrix from my previous post:execute-rules

 

What is the intent behind this QV12.10 change?  I’m not sure.

-Rob

Storing a Data Model in a Single QVD

Have you ever thought it might be interesting to store a  Qlik data model into a single QVD?  This can be useful in a number of cases such as:

  • Archiving (and retrieving) data models.
  • Overcoming the “single binary load” restriction.

QlikView Components (QVC) Version 11 introduced two new routines to do just that:

Qvc.ExportModel — Exports all tables of the current model into a single QVD.

Qvc.ImportModel — Import a data model created by Qvc.ExportModel.

Even if you don’t have QVC V11 installed, you can try Qvc.ExportModel right now using  http include.  Add these lines to any QlikView script (instructions for Qlik Sense further on down in this post).

$(Must_Include=https://github.com/RobWunderlich/Qlikview-Components/releases/download/v11.1/Qvc.qvs);
 CALL Qvc.ExportModel
 CALL Qvc.Cleanup

Mind the wrap. The Must_Include should be on one line. Using QVC requires the Qvc.qvs library be included (usually at the beginning of script), CALLing Qvc routines, and CALLing a Cleanup routine at the end of your script.

Assuming this script is included in “Sales Dash.qvw”,  the default exported model QVD will be named “Sales Dash.qvd” in the same directory.

 

Now, to import this QVD model into another qvw, replace the CALL to ExportModel in the above sample with:

CALL Qvc.ImportModel('Sales Dash.qvd')

The original model will be reconstructed as individual tables.

Qvc.ExportModel has three optional parameters:

CALL Qvc.ExportModel(['qvddir'],['qvdname'],['addTimestamp']);
Parameter Number Parameter Description
1 String. Optional. Relative or absolute directory where the model QVD will be stored. If relative, it follows the same rules as the STORE script statement for relative directory.
2 String, Optional. Name for the model QVD. If omitted, the name of the QVW will be used. For example, if QVW is “Sales.qvw”, then QVD will be “Sales.qvd”.
3 String, Optional. 1/0 True/False. If True, a timestamp of the form _YYYYMMDDhhmmss will be appended to the QVD name. Default if omitted is False.

 

Qlik Sense has no default path  so parameter #1, a lib:// for the QVD should be specified.  Alternatively, if a lib has been established with a DIRECTORY statement, parameter 1 can be omitted.

Qlik Sense will require a web file Connection for the http Must_Include.

webfile-connection

After defining the web connection and having an appropriate folder connection to store the QVD in,  Qlik Sense script would look like this:

$(Must_Include=lib://QvcWeb);
 Call Qvc.ExportModel('lib://QVData')
 CALL Qvc.Cleanup;

 

That’s all there is to it!  If you are already using QVC, I hope you’ll find these routines a welcome addition to the library.  If you are new to QC, explore more at QlikviewComponents.org.

-Rob

Thanks to Jörgen Peerik for raising the single-QVD export idea during a QVC class. 

 

Preparing your script for QV12

Summary: I provide a tool to check your script for compatibility with QlikView version 12. 

I’ve blogged about a couple of script changes in QV12 here.  Since then I’ve also noticed that the $(include) statement is also affected by the Directory statement. That is, if the script below works in QV11, it will not work in QV12:

DIRECTORY Data;
 $(Must_Include=config.txt);

This is because QV11 looks for the file in the working directory where the QVW is, whereas QV12 will respect the DIRECTORY statement and look in the Data directory.

To summarize compatabilty considerations for QV12:

How will you know if you have existing  script that may be impacted by these changes in QV12?  In an earlier post, I introduced the Script Repository tool which can be used to search script across QVWs.

You can use the tool to  search for potential issues.  But I thought I would make it a bit easier by adding a dedicated “Version 12 Upgrade Check” sheet that does the searching and highlighting for you.

The chart at the top of the sheet will list any document that has script that should be examined further.  Select a document, press the highlight button and the script of interest will be outlined in yellow.

My guess is that most customers will not have any compatibility issues.  But why take chances?  Be a hero and scan your script before upgrading.

-Rob

Join me at an upcoming Masters Summit for Qlik event in Johannesburg (6-8 Sept) or Austin (10-12 Oct).  In my Advanced Scripting session, in addition to teaching important scripting techniques, we’ll look at methods and tools for managing your “script farm”. 

Touchless Formatting

Summary: I show a scripting technique to assign display formats to loaded data without touching existing load statements. 

I coded in  SAS for many years and always appreciated the FORMAT statement which allows  assigning a display format to a field, independent of loading the field.

FORMAT OrderDate MM/DD/YYYY;

In QlikView and Qlik Sense script, there is an  equivalent that is useful to be aware of.   It’s not a statement, but a little known trick (so little known I’ve never seen anyone but me do it, although I’m sure others have thought if it).

// Load some dummy fields just to assign formats
TempFormatTable:
LOAD
 Date(0, 'MM/DD/YYYY') as OrderDate,
 Date(0, 'MM/DD/YYYY') as ShipDate,
 Num(0, '00000') as PostalCode,
 Num(0, '#,##0.00') as OrderTotal
AutoGenerate 0;

Facts: // Load the QVD
LOAD * FROM data1.qvd (qvd);

DROP TABLE TempFormatTable;  // Drop temp table

The formats assigned in the TempFormatTable will be inherited by any like-named fields in the QVD Load.   I sometimes find this easier than adding formatting function to the QVD Load statement because:

  • It maintains the optimized QVD load.
  • I can include a master list in the TempFormatTable. There is no error if a field doesn’t exist in the QVD.
  • Syntactically simpler.
  • I don’t touch the existing Load statement.

I don’t always format this way, but there are a number of scenarios where the technique is useful. A common application is to change formats from one locale to another. For example, loading a QVD created in Europe (with European formats) and assigning US Date and Number formats.

The technique works for any input source;  SQL, QVD, xls, etc. It works for both QlikView and Qlik Sense.

You may not ever need this tip, but if you do, I hope it saves you some time and makes your coding easier.

-Rob

Want more Tips & Tricks? Join me at an upcoming Masters Summit for Qlik event in Johannesburg (6-8 Sept) or Austin (10-12 Oct).  In addition to our two days of core sessions, Bill Lay’s “Tips & Tricks” on Day 3 always teaches me something new.  

QV12 Timestamp Parsing

Have you noticed something new in QlikView12 and Qlik Sense timestamp parsing? UTC timestamps are automatically understood.

(Note: the output displayed below utilizes the US Date format set in the script as:  SET TimestampFormat=’M/D/YYYY h:mm:ss[.fff] TT’;)

For example, the expression:

=Timestamp('20160504T142523.487-0500')

returns:

5/4/2016 7:25:23 PM

That is, the UTC offset of “-0500” is detected and the returned value is the UTC time, not the local time of 2:25:23 PM.

I can’t find anything in the help beyond an example  for Timestamp# that demonstrates this but provides no detail.

This parsing functionality is particularly useful now that the QlikView Server logfiles use the UTC format for times.

I’m not sure yet if I like the automatic conversion to UTC time.  For example, apps like the QlikView Governance Dashboard now report Session Start or Event times in UTC time, not local time.

It’s nice that the “T” character is understood. If you want local time, it’s easy enough to drop the offset (“-0500”) as

=Timestamp(left('20160504T142523.487-0500', 19))

which returns

5/4/2016 2:25:23 PM

-Rob

 

Scaling Numbers and DSE Tips

Summary: I demonstrate a simple reusable expression to auto scale numbers in QlikView. This leads to an exploration of some of the finer details of dollar sign expansion.

A QVW example to accompany this post is available for download at http://qlikviewcookbook.com/wp-content/uploads/2016/05/ScalingNumbers.qvw.

The QlikView auto-scaling feature selects an appropriate unit – billion, million, thousands — based on the magnitude of the  Y-axis values.  It’s a nice feature  available in Line and Bar charts.  How can we create the same functionality in Text Objects or Straight Tables?

It’s easy enough to use an if() function that tests the magnitude, does any necessary division, and formats appropriately. For example:

if(Sum(Sales)>1E6, num(Sum(Sales)/1E6,'$#,##0.000M')
 ,if(Sum(Sales)>1E3, num(Sum(Sales)/1E3,'$#,##0.000K')
 ,num(Sum(Sales),'$#,##0')
 ))

(The 1E6 is an easier way to write 1000000).

To avoid repeated coding of “Sum(Sales)” I create a reusable variable with parameters in the script like this:

SET vScaleNumber=if($1>1E6, num($1/1E6,'$#,##0.000M')
 ,if($1>1E3, num($1/1E3,'$#,##0.000K')
 ,num($1,'$#,##0')
 ));

Now I can use the variable vScaleNumber in a Text Object as:

=$(vScaleNumber(Sum(Sales)))

The string “Sum(Sales)” will get substituted in  every occurrence of “$1”.  I ‘ll get an appropriately formatted number like:

If I use “$(vScaleNumber(Sum(Sales)))” in a Straight Table expression without label, hovering over the column heading will show me the full substitution in  a tooltip.

I  can see that the “$1” substitution occurs before the expression is evaluated, and the substituted expression looks like:

if(Sum(Sales)>1E6, num(Sum(Sales)/1E6,'$#,##0.000M')
 ,if(Sum(Sales)>1E3, num(Sum(Sales)/1E3,'$#,##0.000K')
 ,num(Sum(Sales),'$#,##0')
 ))

I’ve avoided re-typing “Sum(Sales)”. But I may have a concern about the performance implications of repeated execution of “Sum(Sales)”.  And what about more complex expressions such as “Round(Sum(Sales),10)”?  The comma in that expression will break the syntax as variable parameters always treat commas as parameter separators.

I can fix the comma/performance problem by using Dollar Sign Expansion (DSE) with an “=”.  The “=” will cause the expression to evaluate and pass the numerical result to vScaleNumber.

=$(vScaleNumber($(=round(Sum(Sales),10))))

Checking the expansion in a Straight Table shows:

if(1783150>1E6, num(1783150/1E6,'$#,##0.000M')
,if(1783150>1E3, num(1783150/1E3,'$#,##0.000K')
,num(1783150,'$#,##0')
))

I  see the value of “round(Sum(Sales),10)” has been calculated as “1783150”,  yielding an efficient and syntactically correct expression.

Next  I’ll add a Dimension to the Straight Table.  The row results are incorrect!

The “=” in the DSE caused the Sum expression  to be evaluated only once for the entire chart, yielding the same value for every row.  How to fix?

I will calculate the sum() expression in a n Expression n column, and then hide this column on the Presentation tab. I can then refer to the hidden column:

=$(vScaleNumber(Column(1)))

Once again, the expansion yields an efficient and syntactically correct expression.

if(Column(1)>1E6, num(Column(1)/1E6,'$#,##0.000M')
 ,if(Column(1)>1E3, num(Column(1)/1E3,'$#,##0.000K')
 ,num(Column(1),'$#,##0')
 ))

I started this post by demonstrating a simple expression to format scaled numbers. It’s a function I frequently use.

For more on DSE, see Henric’s post at https://community.qlik.com/blogs/qlikviewdesignblog/2013/11/18/dollar-expansions

A QVW example to accompany this post is available for download at http://qlikviewcookbook.com/wp-content/uploads/2016/05/ScalingNumbers.qvw.

-Rob

QV12: QVD Functions Respect Directory

Summary: QV12 introduces a “breaking change” in how file paths are interpreted by QVD File functions like QvdNoOfRecords.

In QlikView version 11 script, the five QVD file functions were not impacted by use of the Directory statement.  Relative paths given to these functions were always relative to the qvw file, not the current Directory setting.  This is different than the other file functions like FileSize() which considered paths to be relative to the current Directory setting.

In QlikView version 12, the QVD File functions have been changed to respect the Directory setting, making them consistent with the other functions.  I think this is a good idea.  However, it is a breaking change in that your QV11 script may no longer work in QV12 without changes.

Here is how you may have coded a script sequence in QV11:

DIRECTORY data;
STORE  Customer INTO Customer.qvd;
LET vCustomerRows =    QvdNoOfRecords('data\Customer.qvd');

This will not work in QV12. The function will be looking for the qvd in “data\data\Customer.qvd”.  There will be no script error, but the value of “vCustomerRows” will be null. Because QV12 respects the Directory, the correct syntax for QV12 would be “QvdNoOfRecords(‘Customer.qvd’).

This change is probably not a big deal for most customers, but it could be a silent irritant for many.  If your incremental reloads or other conditional code test for the existence of a QVD using QvdCreateTime(), the code could make the wrong decision leading to missing or corrupted data.

Next week I’ll show you a simple way to scan all your qvws for use of QVD functions or other script of interest.

-Rob

 

Authorizing the Script EXECUTE Statement

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

 

Q-On Courses in January

Just a heads up to get in your planning before taking a holiday break.  I’ll be teaching two on-line courses in early January:

January 7  QlikView™ Document Performance Tuning

Learn how to measure and optimize the performance of your QlikView apps. By the end of the session, you will understand how the calculation process in QlikView works, and how data model, expression and chart design impact response times and resource usage.

You will come away with the skills to analyze your own apps and make them run faster.

 

January 6  QlikView™ Components Scripting Library

Speed up your QlikView™ development workflow by making use of the powerful QlikView Components (QVC) scripting library.

QVC can help you build your QlikView™ projects quicker and ensuring a high level of quality in your scripts. The set of ready-made subroutines that QVC provides can be used to perform common script operations, implemented in a flexible way and incorporating best practices.

 

Hope to see you there !

-Rob

Mind The Space

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