Category Archives: Productivity

Productivity, Qlik Sense, Scripting

Expanding the Qlik Sense Editor Pane

18 Comments

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. 

Edit:  Note 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
Productivity, Tools

Exploring Data Lineage with NodeGraph

6 Comments

In the Qlik world, we are frequently faced with questions like:

  • Where did this field come from?
  • What applications (if any) use this QVD?
  • If I change this database table, what applications will be affected?
  • Am I creating QVDs that are not being consumed?

I’ve maintained a QlikView lineage application for my customers over the years.  I was never completely happy with it as Qlik lineage metadata is inconsistent and the critically important field level lineage was never available.

All that changed when I discovered NodeGraph.  Now I’m a NodeGraph fan and partner.

NodeGraph is an add-on for your QlikView and Qlik Sense solutions that allows you to explore, visualize and trace where your data comes from, whether it’s up-to-date, how it’s calculated, and much more.

NodeGraph scans both QlikView and Qlik Sense files and produces a graph of applications and data in a beautiful easy to navigate interface.  Select any node, for example a dashboard QVW, and lines trace the data lineage through transformed QVDs all the way back to the source database.

 

You can search, filter and drill in any direction;  from applications,  QVDs, source tables, fields, charts or SQL queries.

Where did this field come from?  Right-Click any field and select the Field Explorer to get a report of the complete lineage for this field, including all script transformations!

 

I’m looking at a chart that shows a count of Customers.  How was that count calculated? Where did the data come from?  In the Content view I can review the expression and see the lineage for fields used.

 

One of my favorite features is the one-click application documentation.  The tool generates a pretty printable/PDF document suitable for Auditors that describes all data sources, transformations, and expressions used in the selected application.

 

There are more uses and features in NodeGraph than I can cover in a blog post, including governance reports, application usage, GDPR compliance and testing.

If you are coming to Qonnections,  see NodeGraph in action at the TechLab10 booth (#603) in the Discovery Expo.

Learn more about NodeGraph at nodegraph.se. If you have questions or would like to schedule a free demo,  reach out to the NodeGraph team on the website or info@nodegraph.se.  If you are in North America you can contact me directly for questions or to scheduling a demo.

-Rob

Share
General, Productivity, Tools

QViewer Acquisition

25 Comments

I’m pleased to announce that I’ve acquired the excellent QViewer tool from Dmitry Gudkov effective January 1.  I’ll be marketing, developing and supporting QViewer going forward.

QViewer has a stellar reputation as a must have tool for Qlik Developers.  I’m pleased to be taking over such a fine product.

This acquisition is one step in my plan to focus more on software development during 2018.  Stay tuned for other announcements during the year.

Existing licensed QViewer customers should email future support requests to support@panalyticsinc.com.

Happy New Year!

-Rob

Share
Productivity, QlikView, Tools

Mass Editing of QVW Script

6 Comments

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
Productivity, Qlik Sense, Visualization

Quick Sense App in a Page

14 Comments

Summary: Learn an easy method to deliver a Qlik Sense app as an embedded web page. Yes, the spelling of this post’s title is intentional 😉

I just came off a week of Qlik Sense API & Integration training with Nick Webster of Websy.  I’ve been nothing short of impressed with the integration and reuse options of Qlik Sense.

I was intrigued today by a question on Qlik Community asking if there was a way to disallow the application Edit mode in Qlik Sense Desktop.  The poster wanted his students to just “use” an app and not poke around in the design until a later time.  Kind of like the “distraction free” mode  in text editors.

The challenge I gave myself was how quickly could I knock out a solution to this problem?  I’ll detail my solution below and I’ll tell you it is taking me longer to write this blog post than it took to build the solution.

Rather than working at disabling or removing function I didn’t want, I approached the problem as including only what I wanted. My first thought was to create a mashup that represented the entire application.  Then I struck on an even easier approach — use the Qlik Sense “Single Integration API” with a bit of Bootstrap.   The Single Integration API does not require writing any code.  It’s just a URL that displays a single Visualization or complete Sheet.  You can create URLs and experiment with options in the Dev Hub Single Configuration Tool.

Bootstrap is a popular web design toolkit that makes it easier to create responsive and interactive web pages.  Thanks to Nick’s class I understood how Qlik utilizes bootstrap.

A basic URL to display a sheet looks like this:

http://server/single/?appid=myappid&sheet=sheetId&opt=options

Appid is a qvf filename on QS Desktop, or the GUID if using QS Server.  Options allow you to control  things like allowing selections.  A complete list is in the API doc.

With 50 lines of html I created a web page that delivers the full associative experience across seven sheets, including Current selections, Smart search and the Selections tool.

Here’s a screenshot showing my sheet navigation buttons across the top and the Sheet content, including Current selections, immediately below the buttons.

The html file is available for download here.  I used the “Sales Discovery” sample app but it is easy to adapt the file to any application.

There is a single iframe nested in a responsive Bootstrap container.  The buttons simply change the src attibute of the iframe to load a specific sheet .

If you want to try the file yourself you  only need to change the “var appid=” string to point to the app path on your machine.  No web server is required, just double click the html file to launch.  Your server or QS Desktop must be active.  Yes, this file works as is with QS Server.

Nick and I will be showcasing a variety of QS integrations at the Masters Summit for Qlik in Boston Oct 23-25.   Nick will also be presenting  a half day “Qlik Sense Integration” session at the summit that will teach you how to create a basic web page with bootstrap and explore more advanced QS integration options.

-Rob

 

Share
Productivity, Qlik Sense, Visualization

Vizlib: Innovation and Agility

13 Comments

Summary:  As a provider of Qlik Sense visualization extensions Vizlib promises to blend the innovation and agility of open source with the reliability of commercial software.. 

The pace of delivery for visualization in Qlik Sense has been disappointing to some.  Thanks to rich open APIs in QS, much of the slack has been taken up by the community in the form of open source visualizations as seen on branch.qlik.com.   There you can see some remarkable innovation and responsiveness to community requests.

Should you use open source visualizations?  An open source extension may provide just what you need in your project.  But you should consider the unsupported nature of open source and evaluate the risk and consequences of the visualization possibly failing at some point.

When I worked as  a Java developer  my team used the open source Eclipse IDE as our main tool.  We also used a dozen or more open source plugins.  As our plugin library grew, we found that testing and updating plugins between releases was taking an inordinate amount of time, sometimes making us afraid to upgrade the base Eclipse product.   We  turned to a vendor and purchased a bundled version of Eclipse with dozens of plugins tested, supported and verified. Problem solved.

Vizlib is a new company is promising to blend the innovation and agility of open source with the commercial support that many customers demand.  Vizlib is partnering with the best extension authors to produce a library of fully supported high function visualization extensions. Check out some of what they have published so far:

  • A Table object that delivers the functionality of  a QlikView table and much more.
    • Rich Formatting Options just like in Excel/QlikView
    • Conditional show & hide of columns
    • Dynamic Labels
    • Minicharts & Progress Bars

  • An Advanced Text Object that provides the full functionality of the classic QlikView text object  (including actions!) plus additional functionalities like HTML code and icons. 

To date Vizlib has released five visualizations with more on the way. Check out what’s available on the Vizlib download page.  A free trial license is offered that allows you to try any of the extensions in your own installation.  Take a look!

-Rob

Share
Productivity

Web Development for Qlik Developers

9 Comments

I just finished the four day “Web Dev for Qlik Dev” course with Nick Webster of Websy.io.  I rate the course Excellent!

The course focus is to equip  Qlik Developers with a basic understanding of how to use Web Technologies with the Qlik Sense APIs.  The week starts with an introduction to web technologies — HTML, CSS & Javascript.  And while I have some older experience as a web developer, I appreciated the brief review of current standards and practices.

We then moved on to using the Capability APIs for visualization in a mashup. We spent the last two days focusing on the lower level Engine API and the associated enabling technologies such as JSON and Enigma.js.

Lot’s of hands on work through well constructed exercises. Nick offered a lot of practical direction and tips.

I highly recommend the course to anyone who is considering or exploring integrating  Qlik Sense content into existing web apps or other mashup forms.

-Rob

Share
Productivity, Qlik Sense, QlikView, Scripting, Tools

Storing a Data Model in a Single QVD

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.

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
Productivity, QlikView, Scripting, Tools

Preparing your script for QV12

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:

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
Productivity, QlikView, Visualization

Alt States Merged Selections Tip

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.

 

 

Share