Category Archives: Scripting

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

Share

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

 

Share

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

 

Share

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

Share

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

 

 

Share

Product Improvements For Variables?

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

 

Share

Better Calendar Scripts

TLDR: A new Qlik Sense training video uses a tired old-school Master Calendar script. I propose that updated training materials should use an updated script.

I just watched  a new  video by Michael Tarallo of Qlik titled “Understanding the Master Calendar – Qlik Sense and QlikView“.  Before I  pick on the contents of this particular video, I want to  credit  Michael for  producing many excellent training videos that are worth watching and learning from. I  highly recommend them.

The video does a great job of explaining the need for and function of a Master Calendar in your data model. It then goes on to show an actual script.

I can’t discuss Master Calendar without expressing disappointment that Calendar generation is not yet a builtin function in Sense. Something like QlikView Components (QVC) does with the single script line:

CALL Qvc.CalendarFromField('OrderDate');

On to the script used in this new video. I’ll reproduce the entire script below and then comment on the techniques used and suggest some more “modern” approaches.

The video script is  similar to the script used in the current QlikView Developer course . I acknowledge that this script works as is and produces correct results. But I don’t think it should be taught to newbies as good scripting. Here’s the script from the video:

Obsolete Code

1. Why is this field created  and where is it used?

It’s not used. It’s left over from a very old version of the exercise and it doesn’t serve any purpose.

2. Why are we sorting the table? Is this statement useful?

Even if I could think of a good reason why  the Calendar table should be in order, it already is in this order because the TempCalendar was generated in a loop. Statement unnecessary.

Inefficient Code

Loading a Resident table can be very slow for a large table.

 

Experienced scripters use the FieldValues array instead.

What’s the difference? FieldValues only reads the distinct values of a field — maybe a thousand or so for several years of dates. Resident reads every row of the table. For a 10M row fact table, that’s 10M reads and the time scales up linearly. The difference can be dramatic.

Error Prone and Extra Work

Peek(), used on lines 13 & 14,  is one of those functions that fails silently. That is, if you misspell a field or table,  you won’t get a script error. Misspelling a variable will also not generate  a script error. Maybe. Or maybe not. Or maybe you will get a Calendar that starts in year 1899.  Depends on which line you make the spelling error on. If your misspelling does result in a script syntax error, it will be downstream from where you created the problem. There are multiple ways to mess this one up and some very curious potential results.

Don’t forget to DROP those temp tables on lines 15 and 35.

And those varMinDate and varMaxDate variables really should be cleared as well.

You can avoid all the syntax traps and extra cleanup by coding this a as a Preceding Load.  Here’s the same script written as a Preceding Load:

Nothing to remember (or forget) to clean up.  If you misspell a fieldname, you will get an understandable error at the right place. This is the calendar script I wish we would provide to newcomers.

Of course if you’ve attended the Masters Summit for QlikView, you’ve learned all about FieldValues, Preceding Loads and more. If not, attend a summit this Fall in NYC or Copenhagen.

QVC users don’t even get out of bed to generate Calendars. But they know that all that best practice stuff is happening under the covers. If you want to learn more about QVC, join my online class June 4 or a future class at Q-on.bi.

-Rob

Entire script suitable for copying:

MasterCalendar: 
Load 
 TempDate AS OrderDate, 
 week(TempDate) As Week, 
 Year(TempDate) As Year, 
 Month(TempDate) As Month, 
 Day(TempDate) As Day, 
 'Q' & ceil(month(TempDate) / 3) AS Quarter, 
 Week(weekstart(TempDate)) & '-' & WeekYear(TempDate) as WeekYear, 
 WeekDay(TempDate) as WeekDay 
;

//=== Generate a temp table of dates === 
LOAD 
 date(mindate + IterNo()) AS TempDate
 ,maxdate // Used in InYearToDate() above, but not kept 
WHILE mindate + IterNo() <= maxdate;

//=== Get min/max dates from Field ===/
LOAD
 min(FieldValue('OrderDate', recno()))-1 as mindate,
 max(FieldValue('OrderDate', recno())) as maxdate
AUTOGENERATE FieldValueCount('OrderDate');

Share

Qlikview Components (QVC) Training June 4

I’ll be leading a 3.5  hour training June 4 on using the free open source “QlikView Components” scripting library.   The training is held on-line and you can find out more and register at this link on the Qlik-On website.

QV Developers around the world use QVC in their scripting to:

  • Save time.
  • Improve Quality.
  • Implement Advanced Functions.
  • Get it Right the First Time.

Create a master calendar? No problem — one line of script:

CALL Qvc.CalendarFromField('OrderDate');

Fiscal calendar that begins on Month 4? Again no problem:

CALL Qvc.CalendarFromField('OrderDate','','',4);

What was that Set Analysis syntax for month-to-date in the previous year? The Calendar generates a series of variables for common period to period analysis.

=Sum($(vSetPreviousYearMTD) Sales)

That’s all there is to it. Even if you add fields to the Calendar. Even if you use Italian or Norwegian for the calendar field names.

Quick! Write the script to create an AsOf table for field Year-Month.  I’m waiting…   I can’t do it either without rooting around for an old project to copy from. But I can write:

CALL Qvc.AsOfTable('Year-Month');

Here’s are a few more things QVC can do:

  • Load variables from an external file.
  • Load custom icons from a folder.
  • Logging, including rolling external files.
  • Pause mid-script and let you inspect the contents of a table.
  • Incremental Reloads!
  • NVL!

The QVC project was founded in 2012 with the goal of simplifying common scripting tasks and implementing best practices.  The focus is on what we call the “big middle” of scripting tasks. 

The library is continually refined and enhanced with input from QV developers around the world and we expect some exciting new testing routines soon!

I hope you can join me June 4 to learn the details of installing and using QVC. You will leave the class with the confidence and skills to use all the QVC routines and begin supercharging your script!

-Rob

 

 

 

Share

Those Uncaught STORE Errors

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

Share

Using Variables as Metadata

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.

Share