Category Archives: Scripting

Loading Varying Column Names

Summary:  A script pattern to wildcard load from multiple files when the column names vary and you want to harmonize the final fieldnames.  Download example file here.

I’m sometimes wondering “what’s the use case for the script ALIAS statement?”.  Here’s a useful example.

Imagine you have a number of text files to load; for example extract files from different regions.  The files are similar but have slight differences in field name spelling.   For example the US-English files use “Address” for a field name, the German file “Adresse” to represent the same field and the Spanish file “Dirección”.

We want to harmonize these different spellings so we have a single field in our final loaded table.  While we could code up individual load statements with “as xxx” clause to handle the rename, that approach could be difficult to maintain with many variations.  Ideally we want to load all files in a single load statement and describe any differences in a clear structure.  That’s where ALIAS is useful.  Before we load the files, use a set of ALIAS statements only for the fields we need to rename.

ALIAS Adresse as Address;
ALIAS Dirección as Address;
ALIAS Estado as Status;

The ALIAS will apply the equivalent “as” clause to those fields if found in a Load.

We can now load the files using wildcard “*” for both the fieldlist and the filename:

Clients:
LOAD *
FROM addr*.csv (ansi, txt, delimiter is ',', embedded labels, msq)
;

It’s magic I tell you!

What if the files have some extra fields picked up by “LOAD *” that we don’t want?  It’s also possible that the files have different numbers of fields in which case automatic concatenation won’t work.  We would get some number of “Client-n” tables which is incorrect.

First we will add the Concatenate keyword to force all files to be loaded into a single table.   As the table doesn’t exist, the script will error with “table not found” unless we are clever.  Here is my workaround for that problem.

Clients:
LOAD 0 as DummyField AutoGenerate 0;
Concatenate (Clients)
LOAD *
FROM addr*.csv (ansi, txt, delimiter is ',', embedded labels, msq)
;
DROP Field DummyField;

Now let’s get rid of those extra fields we don’t want.  First build a mapping list of the fields we want to keep.

MapFieldsToKeep:
 Mapping
 LOAD *, 1 Inline [
 Fieldname
 Address
 Status
 Client
 ]
 ;

I’ll use a loop to DROP fields that are not in our “keep list”.

For idx = NoOfFields('Clients') to 1 step -1
  let vFieldName = FieldName($(idx), 'Clients');
  if not ApplyMap('MapFieldsToKeep', '$(vFieldName)',   0) THEN
    Drop Field [$(vFieldName)] From Clients;
EndIf
Next idx

The final “Clients” table contains only the fields we want, with consistent fieldnames.

Working examples of this code for both  Qlik Sense and QlikView  can be downloaded here.

I hope you find some useful tips in this post. Happy scripting.

-Rob

 

 

Share

Does Data Sort Order Impact Chart Calc Time?

Lately I’ve been digging into an old Qlik performance question.  How much impact, if any, does the order of Qlik data tables have on chart calc time?  My experience is that for a chart or aggr() cube with a lot of dimension values,  ordering of rows by dimension values can have a significant and measurable effect.

Mike Steedle of Axis Group blogged about the issue  a couple of years ago.  Mike’s post includes a useful subroutine to organize any table by a specific field.

I’ve added my own study and sample files on the topic in this QlikCommunity post.

Mike and I are are working together on the next update to Qlik Sense Document Analyzer.  Mike is keen on analyzing the data model and making useful recommendations.  One of the optimization questions we are studying is whether it is possible to make a solid recommendation on data table organization.

I’m curious to hear what others have discovered on the topic.  Do you have any rules you follow in ordering table rows?   Any thresholds or object/expression scenarios where you find it’s worth the trouble to manage the ordering?

-Rob

 

Share

Loading Variables From Another QVW

I just read a good post by Kamal Kumar Sanguri on QlikCommunity.  Kamal’s post reminded me that managing variables in QlikView has always presented some challenges and over the years various techniques and code snippets have been shared to address those challenges.

Most folks quickly find that maintaining variables in external files  loaded with a script loop is a good approach and resolves common concerns regarding shareability, dollar sign escaping and so on.

Sometimes you encounter a need for an adhoc import or export of variables. Kamal’s post offers some useful code snippets for that.  Several years ago my colleague Barry Harmsen  wrote a post on QlikFix.com that shows some useful macros to manipulate Variables.  Barry and I  subsequently collaborated on a desktop utility that handles both import and export from a menu.  While the download link on the blog is dead, I’ve reposted the utility on the QlikViewCookbook Tools section for download.

Kamal said that there is no way to directly load variables from one QlikView document to another.  That got me to thinking.  It is possible, but gee, no one has ever asked for it.

What’s the use case?  When I want to do an adhoc copy, I use the desktop utility referenced above.  I have seen a number of customers who generate complex calendars into QVDs followed  by  generation of variable for use with the calendar.  Calendar QVD consumers incorporate the variable generation logic with an include file. It works.

Would it be any better if calendar consumers loaded the variables directly from the calendar generating QVW?  I think not because there is possibility of a mismatch between the QVW source and the Calendar QVD.

All that said, maybe you have a use case for loading variables from a QVW?  No one asked, but for the record here is the script to load variables directly from another QVW.

VariableDescription:
LOAD 
 Name,
 RawValue
FROM [..\..\data\StudentFile.qvw] 
(XmlSimple, Table is [DocumentSummary/VariableDescription])
Where IsConfig = 'false' and IsReserved = 'false' // Exclude system vars
// Any addtional filtering here
;

FOR idx=0 to NoOfRows('VariableDescription')-1
 LET vVarname = Peek('Name',$(idx),'VariableDescription');
 LET [$(vVarname)] = Peek('RawValue',$(idx),'VariableDescription');
NEXT idx

SET idx=;
SET vVarname=;
DROP Table VariableDescription;

Happy Scripting!

-Rob

 

 

Share

Expanding the Qlik Sense Editor Pane

Summary: In this post I present a non-intrusive bookmarklet to hide the Sections pane in the Qlik Sense Script Editor to provide more real estate for typing script. 

EditNote in the comments below that hiding the pane can be done out of the box using keyboard shortcuts such as Alt-F11.   That reminds me to read the doc! Nevertheless the concept of bookmarklets is useful and this post is bringing in some interesting contributions. 

I sometimes wish for a larger window in the Qlik Sense Script Editor where we type  statements.  This is especially true when I am projecting and I’ve zoomed my browser to make the text legible to the audience, or I’m saddled with a very low resolution.

The UI allows me to hide the Data Connections pane which provides  more space, but I’m still left with the Sections pane consuming 250 pixels on the left which may not serve me at the moment.

Wouldn’t it be great if I could hide/show the Sections pane on demand, like I can do with  Data Connections? Here’s a simple non-intrusive hack that will allow you to do just that.

Paste the following code in a bookmarklet. If you’re not familiar with bookmarklets, they are small bits of javascript code that can be executed from the bookmarks menu.  If you are using Chrome as I do, it’s as simple as pasting the javascript code in the URL property of a bookmark.  Google to learn more about bookmarklets.  If you are using a browser other than Chrome google to see how to create (if possible) bookmarklets in your browser.   Here’s the javascript:

javascript: (function () {
 var size = ($('.scripteditor-left').css("width") == "0px") ? "250px": "0px";
 $('.scripteditor-left').css("width", size);
 $('.scripteditor-stage').css("left", size);
 }());

Apply the bookmark and here is what the editor looks like now:

Apply the bookmark again and the Sections pane reappears.

 

This is an unsupported hack of Qlik Sense.  If something else does not work in the editor, you should refresh the browser — which will completely remove any effects of the bookmark — before suspecting or reporting that Qlik Sense has a defect.

This is  a very clean technique to add behavior because we have not modified any Qlik Sense files.

I file my QS bookmarklets in a Bookmarks Bar folder named “QS” which gives me easy access in a dropdown to mods I’ve created for Sense.

Here’s another bookmarklet I find useful.  This one opens the Qlik Sense script log folder for QS Desktop.  My userid in the path is hardcoded, you would of course update to the correct folder name for your machine.

file:///C:/Users/rob/Documents/Qlik/Sense/Log

Please share if you develop additional useful bookmarklets for Qlik Sense.

-Rob

 

Share

AutoNumber vs AutoNumberHash128

Summary:  AutoNumberHash128(A, B) runs about 30% faster than AutoNumber(A &’-‘ & B).

It’s a common practice to use the script AutoNumber() function to reduce the storage required for large compound keys in a Qlik data model. For example:

AutoNumber(A & '-' & B) as %KeyField

As a standard practice, we generally include a separator like ‘-‘ to ensure ‘1’ & ’11’ does not get confused with ’11’ & ‘1’.

The AutoNumber process can add significant run time to a script with many rows.

I’ve always wondered what the AutoNumberHash128() function was good for.

AutoNumberHash128(A,B) as %KeyField

This function first hashes A & B and then autonumbers the result. The end result is the same as the first example given using AutoNumber().  I find the AutoNumberHash128 syntax a bit simpler as a separator is not required.

What surprised me is that the AutoNumberHash128() function runs faster.  Typically about 30% faster than a plain AutoNumber with a concatenated string parameter.

Why is it faster?  The difference is in the function used to create the single value to be autonumbered.  Hash128 is considerably faster than string concatenation (&).

AutoNumberHash128() can take any number of fields, but it does not have an “AutoId” parameter.  The “AutoId” (second parameter) in AutoNumber() is recommended to ensure we get sequential integers when autonumbering more than one key field.  Sequential integers are the most memory efficient storage for keys.

Don’t despair.  AutoNumberHash128() will use the “default” AutoId.  That is fine if you are autonumbering only one key.  If you are doing more than one key, use AutoNumberHash128() for your largest — most rows — key and use AutoNumber() with AutoId for the rest.  You will improve the script run time of one key.

Another possible tradeoff when you have many large keys is to use AutoNumberHash128 for all keys and forgo the sequential integer optimization.  You will use only 8 bytes per key value which could be significantly less than the original string keys.

-Rob

 

Share

LET, SET, Quotes

Summary: In Qlik script SET is often a better choice than LET, even when the value contains quotes. 

I sometimes see the LET script statement used when SET would be syntactically  easier and more readable.

A brief review:  SET assigns the given parameter as-is to the variable,  LET treats the parameter as an expression and assigns the evaluated result to the variable.

SET x = 1+3;  // x is "1+3"

LET x = 1+3; // x is "4"

I frequently see a variable assignment like this:

LET eSales='sum(Sales)';

eSales stores an expression that will be used later in charts.  It could also be written (simpler in my estimation) as:

SET eSales=sum(Sales);

So far just a matter of style, but the difference becomes clear when we have quotes as part of the string, for example, “Region={‘US’}”.   As LET requires a quoted string,  embedded quotes require some sort of escaping.  In QV10 and earlier, a common way to write this with LET would be:

LET x = 'Region={' & chr(39) & 'US' & '}';

Not real pretty. Many people carry over this style even though QV11 introduced two single quotes to represent an embedded single quote.

LET x = 'Region={''US''}';

Easier to read for sure.  But I think it’s even easier with SET.

SET x = Region={'US'};

That’s it. No special escaping required, just type it as it should be.  What about those quotes? Shouldn’t SET strings be enclosed in quotes?

I find the documentation on SET to be thin, but here is the rule as I understand it.

Single or Double quotes in a SET statement require no special treatment as long as they are balanced (even number of quotes).

SET x = Region={'US'},Product={'Shoe'};  // Valid

SET x = Region={"U*"},Product={'Shoe'}; // Valid

SET x = I won't go;   // Invalid

If the quotes are unbalanced (odd number), then the entire string needs to be enclosed in quotes or brackets.  Use double quotes if we are enclosing single quotes.

SET x = "I won't go";

SET x = [I won't go];

I always favor SET over LET unless I truly want an evaluation.  An exception to this is the string “$(” which will trigger an Dollar Sign Expansion, even in SET.

-Rob

For more on character escaping in Qlik from HIC see https://community.qlik.com/blogs/qlikviewdesignblog/2015/06/08/escape-sequences

Share

QV12 REM Logging Change

Summary: QV12 no longer prints the REM statement to the Document Log.

In QV11 “//” and “/*” comments do not appear in the Document Log, but “REM” comments do appear in the log.  I found REM useful to provide some documentation in my logfile or record which branch was taken in an IF-THEN-ELSE.

QV12 has changed the logging of REM.  The REM statement will now appear in the log obfuscated as a series of asterisks  For example, the statement

REM  Beginning of weekly load;

will appear in the log as

2017-05-18 14:24:34 0005 *** *** ******

My understanding is that the change was made to support security standards, as someone could REM a CONNECT string or other sensitive data thereby exposing it in the log.

To provide log documentation, use the TRACE statement instead.  I don’t find TRACE as pretty as REM because TRACE generates double lines, but it will do the job.

I maintain a collection of upgrade notes for QV12 that you can download here to assist in your planning.  Please do read all the Qlik doc — release notes and help site — as well.

-Rob

 

Share

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

Share

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. 

 

Share

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”. 

Share