Category Archives: QlikView

Document Analyzer Batch Analysis

Jul 18, 2017 Rob Wunderlich 13 Comments

I’ve received several requests to provide a batch interface to the popular QV Document Analyzer tool that will provide for analyzing multiple QVWs with a single command. It’s now available for download here.

The script is a windows cmd file. Because many browsers block download of cmd files, I’ve provided it with a “txt” extension. Rename to “DABatch.cmd” after downloading.

The usage from the command line is:

DaBatch.cmd somedir

where “somedir” is a directory of QVWs to be analyzed. Each QVW in the directory will be processed by Document Analyzer and the results will be saved for later review.

Before running, there are a number of configuration variables in DABatch.cmd you will want to review and modify as necessary.

REM *** Path to QV.exe executable ***

SET qvDir=C:\Program Files\QlikView\Qv.exe

This is location of the QV Desktop executable. The provided value is the default location for most users and is typically appropriate as-is.

REM *** Path to DocumentAnalyzer.qvw. Note that v3.6 or later is required! ***

SET DaPath=DocumentAnalyzer_V3.6.qvw

Where is the Document Analyzer.qvw to be found? Note that DA V3.6 or later is required by DABatch.

REM *** Directory to store DocumentAnalyzerResults QVDs and QVWs. Will be created if it doesn't exist *** SET DaResultsDir=C:\temp\MyDaResults

Specify the directory where analysis results will be saved. If this directory does not exist, it will be created.

REM *** Should the analyzer results be stored in a QVD (YES/NO)? ***
SET SaveResultsInQVD=YES

Do you want to save the DA results in a QVD for later analysis by the DaCompareTool.qvw? The default of “YES” is usually appropriate here. QVD result files include a timestamp so you will always get new files for each run. Change to “NO” if you don’t want result QVDs.

REM *** Should the analyzer results be stored in a QVW (YES/NO)? ***

SET SaveResultsInQVW=YES

If “YES”, a DA QVW will be saved for each analysis and named “DocumentAnalyzer_your_qvwname.qvw”. If a file exists with this name, it will be overwritten. If you don’t want individual DA QVWs, change this variable to “NO”.

After launching DABatch, you will receive one prompt:

Analysis Title? <ENTER> for 'Baseline'

The prompt is requesting a title to be assigned to the Result QVDs that will be consumed by DaCompareTool. To accept the default of “Baseline”, press <Enter>. Otherwise type a new value and press <Enter>.

If you have set “SET SaveResultsInQVD=NO” as a configuration option, the title value is irrelevant. (Perhaps I should not prompt in that case; next version?).

While the script is running Document Analyzer windows will be launched for each QVW and progress message displayed. It’s best to keep your hands off the keyboard to get proper timings. Good time to get that coffee.

When execution is complete you’ll see a summary message.

*****************************************************
Batch Analysis complete. 3 QVWs analyzed.
*****************************************************

You can now review each”DocumentAnalyzer_your_qvwname.qvw” file or load the result QVDs into DaCompareTool.qvw for comparative analysis.

Please let me know in the comments section if you can think of enhancements that support your use case for DA batch analysis.

-Rob

QlikView, Scripting

QV12 REM Logging Change

Jun 27, 2017 Rob Wunderlich 5 Comments

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

Qlik Sense, QlikView, Tools, Uncategorized

QlikView to Qlik Sense Convertor

Jun 26, 2017 Rob Wunderlich 7 Comments

Are you migrating QlikView Apps to Qlik Sense? Have you tried the new QlikView Convertor tool in QS 3.2?

The QV Convertor tool is available from the Dev Hub in QS 3.2+. It’s a pretty slick tool that converts QV Variables, Dimensions and Expressions to Master items and converts QV charts to Master QS Visualizations. It does not attempt to replicate the sheets, but expects you to place the visualizations on sheets yourself as required.

It’s a very useful tool with a good UI that allows for filtering and limiting what gets converted.

At the Atlanta Qlik Dev Group meeting on July 13 I’ll be demonstrating the tool and presenting some tips and considerations for doing conversions. They’ve given me two hours (!) to speak so I’ll be covering several other topics as well.

-Rob

Qlik Sense, QlikView, Tools

Cookbook Tools Updates

Apr 24, 2017 Rob Wunderlich Leave a comment

Just a quick note about some recent updates to the Tools available on QlikViewCookbook.com

QV Document Analyzer V3.5
- Added new computed field, “Expression Table Count” that identifies how many tables are involved in a given expression. Expressions that use data from more than one table typically run slower then those with all data in a single table.
- Added “Like Objects Count” attribute for Objects, identifying candidates for linked objects.
- Bug fixes.
Copy Groups Utility V2 allows for copying groups within the same QVW.
Script Log Analyzer V1.6 can analyze reload logs from both QlikView and Qlik Sense, Desktop and Server versions. Interface is available in four languages.

-Rob

QlikView, Scripting

QV 12.10 “Can Execute External Programs” is back

Dec 2, 2016 Rob Wunderlich 1 Comment

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:

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

-Rob

Productivity, Qlik Sense, QlikView, Scripting, Tools

Storing a Data Model in a Single QVD

Nov 8, 2016 Rob Wunderlich 18 Comments

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.

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.

Productivity, QlikView, Scripting, Tools

Preparing your script for QV12

Jul 20, 2016 Rob Wunderlich 1 Comment

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:

QV12 changes the way script EXECUTE statement is authorized.
QVD*() functions respect the DIRECTORY statement.
$(include) and $(Must_include) respect the DIRECTORY statement.

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

Productivity, QlikView, Visualization

Alt States Merged Selections Tip

Jul 18, 2016 Rob Wunderlich 4 Comments

Summary: I suggest a simpler syntax for merging selections from multiple states.

You may be familiar with the “Product Grouping” example from the “What’s new in QlikView 11.qvw” sample. It’s a great beginner demo of using Alternate States for comparative analysis.

In this visualization, a user can select two sets of values from the [Product Sub Group] field. The two different sets are represented by two states, [Group 1] and [Group 2]. The blue [Group 1] bar is plotted on the bar chart using this expression:

sum({[Group 1]<Region = $::Region, [Sales Rep] = $::[Sales Rep], Path = $::Path, Year = $::Year, Quarter = $::Quarter, Month = $::Month>} Sales)

[Group 1] as the Set Identifier indicates we want to start with the selections in [Group 1]. We then modify, or add, selections from the default state by referencing each field with the “$::fieldname” syntax.

The green [Group 2] bar is created with a similar expression, the only difference being [Group 2] as the Set Identifier.

The “$::fieldname” syntax works, but it forces us to list every field. Listing every field can get difficult. Is there an easier, more generic method? Yes, if we redefine the problem as: All selections from the Default state except for [Product Sub Group].

sum({[Group 1] * $<[Product Sub Group]=>} Sales)

Breaking the Set Expression down:

[Group 1]    // Group 1 selections

*    // Intersected with

$<[Product Sub Group]=>   // All selections from Default except [Product Sub Group]

I’m not suggesting the sample is wrong. The “$::” syntax is useful to know and is required when you want to reference only specific fields. I’m posting this alternative because I see people copying this more complex $:: syntax when the simpler syntax would suit their application.

-Rob

Join me at an upcoming Masters Summit for Qlik event in Johannesburg (6-8 Sept) or Austin (10-12 Oct). Oleg Troyansky’s Set Analysis and Advanced Aggregation session provides more useful tips and advanced techniques in using Alternate States.

Productivity, Qlik Sense, QlikView, Scripting

Touchless Formatting

Jul 6, 2016 Rob Wunderlich 24 Comments

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 1;

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.

Qlik Sense, QlikView, Scripting, Visualization

QV12 Timestamp Parsing

Jun 15, 2016 Rob Wunderlich 8 Comments

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

Qlikview Cookbook

Category Archives: QlikView

Document Analyzer Batch Analysis

QV12 REM Logging Change

QlikView to Qlik Sense Convertor

Cookbook Tools Updates

QV 12.10 “Can Execute External Programs” is back

Storing a Data Model in a Single QVD

Preparing your script for QV12

Alt States Merged Selections Tip

Touchless Formatting

QV12 Timestamp Parsing

Recipes for Qlikview Success