Category Archives: Tools

Mass Editing of QVW Script

Summary: In this post I describe a process using freely available tools to apply changes to all scripts in a set of QVW files.

So you have a lot of QVWs. And now you are asked to identify and make updates to all scripts to support changes such as:

  • A change in QVD or other file naming.
  • Changes in file paths due to a server move or directory restructuring.
  • Updating file paths to use variables.

If you are super cool, all those items are represented by variables, changes are handled by updating a single include file and you can relax and stop  reading this post. For the rest of us, read on.

You can scan and search all your script using my Script Repository tool.  That will allow to identify where changes are required, but do you have time to edit every QVW and make the changes?  Easy enough for a few, but what about when you have dozens?

QlikView Desktop has a useful facility we can leverage for mass changes; the “-prj” folder.  If a folder named qvwname-prj  (case sensitive) exists in the same folder as the QVW, when the QVW is saved, QV Desktop will write a set of text files representing the structure of the QVW to the -prj folder.  One of those files is “LoadScript.txt” which contains the load script.

When QV Desktop opens a file, it checks for the existence of a companion -prj.  If found, it populates the QVW with the contents of the files in the -prj.  If we change one of those files, for example “LoadScript.txt”,  that change will be inherited by the QVW.

Let’s walk thorough a scenario where we can utilize this feature to update the scripts of an entire set of QVWs.  I’ll utilize free tools that will make the process easier.

My sample problem is this: I have inconsistent QVD naming conventions. We’ve decided that “DimCustomer.qvd” shall henceforth be known simply as “Customer.qvd”. I’ll need to update the script that generates the QVD as well as all readers of the QVD.

I will accomplish this update in four steps:

  1. Create -prj folders for all QVWs.
  2. In the “LoadScript.txt” files replace “DimCustomer.qvd” with “Customer.qvd”.
  3. Rebuild the QVWs with the updated -prj.
  4. (Optional) Delete the -prj files.

The sample I’ll use for this post is relatively small to keep the demo simple.  But I’ve used this technique to process hundreds of QVWs at a time incorporating several different script edits.

I have a directory of QVWs that looks like this:

 

 

In the SubFolder “Loaders”, there are additional QVWs.

 

 

I’ll need a -prj folder for each QVW. I  can create the -prj manually, but this is where I can leverage the PrjTool to make life easier.  You can download the PrjTool from the Tools section of this site.  (Note: If you received a copy of PrjTool from the Masters Summit, please download this newer version as it contains important updates.)

PrjTool requires a Directory as input and the selection of one of  three functions:

  • BuildPrj: For all QVW files found in the specified Directory, create a -prj folder.  This includes opening and saving the QVW to populate the -prj.
  • CreateFromPrj: For all -prj folders found in the specified Directory, open and save the QVW to update the QVW with contents of the -prj.  If no QVW exists, a new one will be created.
  • DeletePrj: Delete all -prj folders found in the  specified Directory.

I’ll start by specifying the Directory that holds our QVWs and selecting the BuildPrj function.  Press the Execute button and the script will launch. The execution may take some time as each QVW has to be opened and saved. Good time to go for a coffee.

When the execution completes the log window will be filled with messages listing the -prj folders created by the tool.

 

If we examine the directory again we will see the new -prj folders.

 

Our next task is to edit the LoadScript.txt files. We can use any editor capable of searching and replacing across multiple files.  For this demo I will use the free NotePad++ editor.   From the NotePad++ menu, launch “Search” , “Find in File”.  In the search dialog I specify the Directory  and  the search and replace strings. I’ll also limit the search to the LoadScript.txt files.

 

After pressing “Find All”,  I’ll get a list of search results.  I can double click any of the results to open the file for further examination.

 

When I’m satisfied that I’m going to make the correct updates, I again launch “Find in Files” and press “Replace in Files”  to perform the update.

Now I’ll use the PrjTool again to update the QVWs with the updated -prj files.  I run the tool again, this time selecting the “CreateFromPrj” function.  Again, if you have a lot of large QVWs, be patient while the tool runs.  The resulting log messages will inform me of the updates.

We’re done!  All QVWs now contain the updated load script.  Optionally we can run the tool again with the “DeletePrj” function to delete the generated -prj folders.

You should always perform this kind of mass update activity on copies of QVWs and audit the results.  Also, never use -prj folders in production.  Server reloads do not recognize -prj folders.

-Rob

 

Share

Document Analyzer Batch Analysis

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
Share

QlikView to Qlik Sense Convertor

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

Share

Cookbook Tools Updates

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

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

Preparing for QlikView Version 12

There are a number of reasons you may want to upgrade to QV12, I’ll be posting about them in the next few weeks. Today’s post is specifically about what you must address to upgrade to QV12.

There are two items I’m aware of that you must be aware of and consider before upgrading.

  1. QlikView version 12 Server changes the default permission for the  EXECUTE script statement More details in this post.
  2. In QlikView 12, QVD functions now respect the DIRECTORY statement. This is a breaking change that may cause some of your QV11 script to return incorrect results or take incorrect conditional actions. More information in a post here.

So again, I’ll be blogging about new features you may want to take advantage of,  but above are the only two items I know of that you must consider before upgrading.

How do you know if you are using the impacted EXECUTE statement or QVD* functions? Perhaps you already have a tool in place to answer that question. But if you don’t, my colleagues at the Masters Summit for Qlik are making the Script Repository Tool (“ScriptRepo”) available for public download.

If you have attended a Masters Summit, then you already have a copy of ScriptRepo  in your takeaways.  if not, we would love to have you attend a Summit. But the Masters Summit Team understands the universal need for a tool like ScriptRepo whether you can attend the Summit or not.

The “ScriptRepo” tool extracts script from a directory of QVWs and then allows you to do a global search for script of interest such as “Execute”. Plug in the directory path that contains qvw files,  and a temporary work directory to extract the script into:

384

Press the “Extract & Reload” button and each QVW file in the input directory will be opened and the script extracted. The open is done with “nodata”, so the extraction runs relatively fast — about one qvw per second.

After extraction, you can use your favorite search tool to scan the output directory, or use the builtin QlikView search & display from the “Script Search” sheet.

After searching for “customer” and selecting a candidate qvw:

276

 

I hope to see you at the Milan Summit in April 2016 or a USA location in Fall 2016.  If you have questions about the Summit or using the ScriptRepo tool, let us know through the QlikView Cookbook Contact form or the Masters Summit Contact page.

-Rob

Share

QVPR Analysis Tool Adds Task Visualization

Ralf Becher has created a nice Icon Graph extension and used it to visualize Reload task structure within my QVPR Analysis tool. I’ve integrated Ralf’s script into V2 of QVPR Analysis and all you need to do is download and install the Icon Graph extension from https://github.com/ralfbecher/QlikView_Extension_IconGraph.  Links are also provided in the QVPR Analysis qvw.  Select some Tasks or Documents and you’ll get a visualization like the following.

Ralf Becher has done some really interesting work with Graphs and Neo4J. Ralf is presenting a session (T56) at Qonnections on Wednesday afternoon if you want to learn more about using Graph models and visualizations in Qlik.

-Rob

 

Share

Major Update to Document Analyzer

I’ve been publishing the free Qlikview “Document Analyzer” tool since 2009. I’ve just released a major update to DocAnalyzer. You can download Document Analyzer V2 from the Qlikview Cookbook website

DocAnalyzer is a tool to help tune the performance — response time and resource requirements — of a Qlikview QVW. DocAnalyzer can identify unused Fields and pinpoint slow charts.

How do you use DocAnalyzer? After identifying the QVW to be analyzed. press the “Extract Metadata” button to begin the analysis process. When the extract is complete, you’ll receive a popup message. The extract can take anywhere from a few seconds to several minutes for documents with many thousands of objects.

 

 

When the extract is complete, you must run a Reload of Document Analyzer. A button is provided for this or you can do Ctrl-R.

What can you discover in the analysis? Here are a few items.

– The Fields sheet will give you an overview of what fields are used and unused and where they are used — Dimensions, Expressions, Variables, etc.

A Listbox is provided with suggested DROP FIELD script statements for the unused fields.

Select $date and $timestamp in “Field Tag” listbox. Examine the “Symbols Size” column in the Field References chart. Dates and Timestamps can be stored in 8 bytes, but frequently get converted to longer string representations during loading. The impact is only significant if there are a large amount of Distinct Values.

– The Memory sheet provides an overview and detail of RAM requirements and estimates how much memory could be saved by dropping unused fields.

 

 

 

 

– The Object sheet lists all Objects along with their Calc Time and RAM Size.

 

 

 

 

Selecting a single object will enable a “Goto Object” that open the target document and Activate that object so you can examine it’s properties.

There are many other areas you can examine such as the use and performance of Calculated Dimensions, Groups, Expression contents and what properties use those expressions.

Document Analyzer is one of the Tools that Oleg Troyansky will present in his “Performance Tuning” session at the upcoming Masters Summit for Qlikview in Amsterdam Oct 1. I hope you can join us there for more performance tips and a chance to learn advanced Qlikview development skills.

-Rob

Share

Analyzing Your QVPR

The Qlikview Management Console (QMC) is used to define Reload and Distribution tasks for your Qlikview documents. These definitions are stored in a set of XML files — the Qikview Repository (QVPR) — located in C:ProgramDataQlikTechManagementServiceQVPR on your server.

The QMC provides a good overview of scheduled tasks, but a better analysis is sometimes need to answer questions like:

  • What documents are being scheduled on a Monthly schedule? Weekly?
  • What documents are using Dynamic Distribution (reduction) and what field controls the distribution?
  • Which administrator modified this task and when?
  • What documents are being distributed to group Accounting?
  • How many documents are being distributed as PDF?
  • Do I have logical errors in my QVPR?
  • When was my server upgraded?

I’ve produced a “QVPR Analysis” tool that loads the QVPR XML files and makes them available for Qlikview analysis to answer the above questions and more. You can download the QVPR Analysis tool  from the “Tools” menu of QlikviewCookbook.com.

The download link above contains some screenshots from the tool (names are scrambled in the images).

You can load directly from the server QVPR folder or an offline local copy obtained from the server. The offline capability makes the tool especially useful for remote consultants who can analyze a customer’s configuration without having QMC access.

This is the same tool provided to Masters Summit for Qlikview attendees last year. If you’ve already received a copy, no need to download again. I plan to continue enhancing the tool and will release further updates on QlikviewCookbook.com.

The analysis is pretty much text and tables which has suited my needs just fine. I’ve used the tool several times to solve some tricky customer problems or get someone out of a jam.

If you have Qlikview Publisher, you’ll get detailed information on Distribution tasks. If you don’t have Publisher, you won’t have distribution information but you’ll still get useful information on reload schedules and QVPR structure.

If you build some new analysis or find the tool useful, drop me a comment.

-Rob

Share